DAX (Data Analysis Expressions) is the formula language of Power BI. It looks like Excel formulas — but it operates on tables and relationships, not individual cells. This single difference in thinking is what trips up every beginner. Once you internalize it, DAX becomes logical and powerful.
The Core Concept: Filter Context
Every DAX measure is evaluated inside a filter context — the combination of filters applied by report slicers, visual filters, and row/column selections. When a user clicks a region on a map, DAX recalculates every measure on the page for that region automatically. Understanding this is 80% of understanding DAX.
Measure vs. Calculated Column
Always prefer measures over calculated columns. Measures are calculated on-the-fly using the current filter context. Calculated columns are computed once during refresh and stored in memory. Use calculated columns only when you need row-level values for filtering or relationships.
The 5 Measures Every Dashboard Needs
1. Total Sales (Basic SUM)
Total Sales = SUM(Sales[Amount])
Always create measures, never drag raw columns into values. This gives you filter context control.
2. Sales Last Year
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
Requires a proper date table with continuous dates. Mark it as a Date Table in Power BI Desktop.
3. Year-over-Year Growth %
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY], 0)
DIVIDE handles division by zero automatically and returns the third argument (0) instead of an error.
4. Year-to-Date Total
YTD Sales = CALCULATE([Total Sales], DATESYTD(Dates[Date]))
Shows cumulative performance from January 1st to the currently selected date.
5. % of Total (ignores current filter)
% of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products[Category])))
ALL() removes the category filter, so the denominator always equals grand total — giving you the correct contribution percentage per category.
The CALCULATE Function
CALCULATE is the most important function in DAX. It evaluates an expression in a modified filter context. Every time intelligence function (SAMEPERIODLASTYEAR, DATESYTD, etc.) works by wrapping the base measure in CALCULATE with a modified date filter. Once you truly understand CALCULATE, advanced DAX becomes a matter of knowing which filter modifier to apply.
Building Your First Date Table
Time intelligence functions require a dedicated date table with one row per day, no gaps. Use Power Query to generate it:
= List.Dates(#date(2020,1,1), Number.From(DateTime.LocalNow()) - Number.From(#date(2020,1,1)) + 1, #duration(1,0,0,0))
Add Year, Month, Quarter, Week Number columns. Mark it as a Date Table and link it to your fact table on the date key.
