Aggregation
SUM(Table[Column]) -- total of a column
AVERAGE(Table[Column]) -- average value
COUNT(Table[Column]) -- count of rows (non-blank)
COUNTROWS(Table) -- count of rows in a table
DISTINCTCOUNT(Table[Column]) -- count of unique values
MIN(Table[Column]) -- smallest value
MAX(Table[Column]) -- largest valueFiltering
CALCULATE(expr, condition) -- recalculate with a filter applied
FILTER(Table, condition) -- returns a filtered table
ALL(Table) -- removes all filters on a table
ALLEXCEPT(Table, Column) -- removes filters except on given column💡
CALCULATE is the single most important function in DAX — almost every advanced pattern below is a variation of CALCULATE with a specific filter condition.
Time Intelligence
TOTALYTD(SUM(Sales[Amount]), Dates[Date])
SAMEPERIODLASTYEAR(Dates[Date])
DATEADD(Dates[Date], -1, MONTH)
-- Year-over-year growth %
YoY % =
DIVIDE(
[Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])),
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
)Logical
IF(condition, true_result, false_result)
SWITCH(
TRUE(),
[Sales] > 10000, "High",
[Sales] > 5000, "Medium",
"Low"
)Text & Info
CONCATENATE(Table[First], Table[Last])
FORMAT(Table[Value], "Currency")
ISBLANK(Table[Column])
DIVIDE(numerator, denominator, alternate_result) -- safe division, avoids /0 errors💡
Use DIVIDE() instead of the / operator whenever there's any chance of dividing by zero — it avoids ugly error values in your visuals.