Power BI Desktop – DAX

Goals

Creation of advanced measures in DAX.

Prerequisite

Basic knowledge of Power BI Desktop (basic DAX and creating visuals).

Contents

Reminders

• Create a calculated column,

• Create a date table using a formula,

• Create simple measures (SUM, DISTINCTCOUNT),

• Use CALCULATE to change the filter context within a formula.

Function spotlight: Calculate

• Use CALCULATE with column filters,

• Use CALCULATE with table filters (ALL, ALLSELECTED, REMOVEFILTERS, FILTER).

Iterator Functions

• Use SUMX to create a measure based on a calculation at table level (sum of field1*field2, etc.),

• Use CONCATENATEX and DISTINCT to create a measure that concatenates all possible values for a field,

• Use RANKX to calculate Ranks,

• Use AVERAGEX, SUMMARIZE and ADDCOLUMNS to create an average of sums by category.

Time Intelligence

• Calculate Year-to-Date and Quarter-to-Date values (TOTALYTD, TOTALQTD),

• Compare a period to the same period one or more year(s), quarter(s) or month(s) earlier (DATEADD),

• Compare a period to its parent period – quarter to year, month to year, etc. (PARALLELPERIOD).