Methods to create DAX measures

DAX Functions in Power BI

Carl Rosseel

Curriculum Manager

Implicit vs explicit measures

Implicit

Automatically created by Power BI
Comes directly from the Database
E.g.: If we drag Sales to values of a table, Power BI will automatically sum it
Using a dropdown menu we can define the aggregation: sum, average, count, ...

Explicit

Writing measures in an explicit way
E.g.: Total Sales = SUM(Orders[Sales])
Offer flexibility

Why explicit measures are preferred

Reduces confusion of what a measure is or does
- Total Sales = SUM(Orders[Sales])
- Total Sales is more clear than Sales (SUM, AVG, MIN, ... ?)
Reusable within other measures
- Total Sales East = CALCULATE([Total Sales],Orders[Region] = 'East')
Can be given a custom name to explain its functionality
Makes maintenance of complex models more sustainable

Best practices

Keep DAX measures grouped together:
- Measures are free to move to any table
- This is in contrast with calculated columns, which belong to a specific table

Screenshot 2021-07-22 at 11.22.15.png

Format and comment with DAX:
- Use indentations to increase understanding
  - Shift Enter to start a new line
  - Tab to indent
- Add comments after a //

Use variables to improve your formulas

Stores the result of an expression as a named variable
Can be used as an argument to other measure expressions
Four main advantages:
- Improve performance
- Improve readability
- Simplify debugging
- Reduce complexity

Syntax:

VAR <name> = <expression>
- Name = The name of the variable
- A DAX expression which returns a scalar or table value
- Followed by a RETURN statement

Use variables to improve your formulas - example

Calculate the sales from last year and store it as a variable

VAR 
SALESPRIORYEAR = CALCULATE([SALES],SAMEPERIODLASTYEAR('DATE'))
RETURN

Use the variable in a formula

Sales growth = [Sales] - SALESPRIORYEAR

Use variables to improve your formulas - example

All together it would look like this:

Sales growth = 

VAR 
SALESPRIORYEAR = CALCULATE([SALES],SAMEPERIODLASTYEAR('DATE'))
RETURN

Sales growth = [Sales] - SALESPRIORYEAR

Let's practice!

DAX Functions in Power BI

Preparing Video For Download...