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
DAX Functions in Power BI

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
DAX Functions in Power BI

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 //
DAX Functions in Power BI

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
DAX Functions in Power BI

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
DAX Functions in Power BI

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
DAX Functions in Power BI

Let's practice!

DAX Functions in Power BI

Preparing Video For Download...