Part 1 is the 10 DAX measures you'll write in almost every report. Part 2 is 20 quick answers for the moments you get stuck in Power Query, the model or a visual.
Save it. Build faster.
10 DAX measures to know
- 01
SUM
Total Sales = SUM(Sales[Amount]) - 02
DISTINCTCOUNT
Customers = DISTINCTCOUNT(Sales[CustomerID]) - 03
DIVIDE
AOV = DIVIDE([Total Sales], [Orders]) - 04
CALCULATE
South = CALCULATE([Total Sales], Region[Name] = "South") - 05
FILTER
Big = CALCULATE([Orders], FILTER(Sales, Sales[Amount] > 5000)) - 06
ALL
% Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product))) - 07
SUMX
Revenue = SUMX(Sales, Sales[Qty] * Sales[Price]) - 08
TOTALYTD
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) - 09
SAMEPERIODLASTYEAR
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) - 10
RANKX
Rank = RANKX(ALL(Product[Name]), [Total Sales])
TOTALYTD, SAMEPERIODLASTYEAR and DATEADD need a date table marked as a date table.
Power BI: how do I …
- 01
Remove duplicates (PQ)
Home > Remove Rows > Remove Duplicates - 02
Split a full name (PQ)
Split Column > By Delimiter - 03
Fix a column type (PQ)
Click the type icon in the column header - 04
Replace null with 0 (PQ)
Transform > Replace Values - 05
Combine 12 monthly files
Get Data > Folder > Combine & Transform - 06
Month columns to rows (PQ)
Select ID columns > Unpivot Other Columns - 07
Make a date table
Date = CALENDARAUTO() - 08
Use it for time intelligence
Table tools > Mark as date table - 09
Link two tables
Model view: drag key to key - 10
Count rows
Rows = COUNTROWS(Sales) - 11
Last month's sales
Sales LM = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, MONTH)) - 12
Show blank as 0
COALESCE([Total Sales], 0) - 13
Ignore the region slicer
CALCULATE([Total Sales], ALL(Region)) - 14
Read the slicer's choice
SELECTEDVALUE(Region[Name], "All") - 15
High/Low label
SWITCH(TRUE(), [Margin] > 0.2, "High", "Low") - 16
Text + number in a card
"Sales: " & FORMAT([Total Sales], "#,0") - 17
Sort months Jan to Dec
Sort by column: Month Number - 18
Show only the top 5
Filters pane > Top N > 5 - 19
Colour bad numbers red
Conditional formatting > Rules - 20
Share with your team
Publish to a workspace
Want the why behind these? See 10 DAX functions you actually need and what a Power BI JD really tests.