DAX
CHEAT SHEET
swipe
🔹 Basic Syntax
Every formula starts with =
= SUM(Sales[Amount]) → Adds values in the column
Functions are in CAPITAL letters
= AVERAGE(Sales[Quantity]) → Finds average
quantity
Columns go inside square brackets [ ]
= SUM(Sales[Amount]) → [Amount] is a column in
Sales table
Measures are used directly
= [Total Sales] + [Total Profit] → Combines measure
Comments explain your formula
// This is total sales
swipe
🔹 Aggregation Functions
SUM → Adds all values in a column
SUM(Sales[Amount])
AVERAGE → Mean of values
AVERAGE(Sales[Amount])
MIN → Smallest value
MIN(Sales[Amount])
MAX → Largest value
MAX(Sales[Amount])
COUNT → Counts numeric values
COUNT(Sales[Amount])
COUNTA → Counts non-blank values
COUNTA(Customers[Name])
COUNTROWS → Number of rows in a table
COUNTROWS(Sales)
🔹 Logical Functions
IF → Returns result based on condition
IF(Sales[Amount] > 1000, "High", "Low")
SWITCH → Handles multiple conditions
SWITCH(Month[Number], 1,"Jan", 2,"Feb","Other")
AND → True if all conditions are true
AND(Sales[Amount]>100, Sales[Amount]<500)
OR → True if any condition is true
OR(Sales[Amount]>1000, Sales[Discount]>50)
NOT → Reverses condition
NOT(Sales[Amount]>500)
swipe
🔹 Text Functions
CONCATENATE → Joins text values
CONCATENATE(Customer[FirstName],
Customer[LastName])
& (Ampersand) → Joins with separator
Customer[FirstName] & " " & Customer[LastName]
LEFT → First N characters
LEFT(Customer[Name], 3)
RIGHT → Last N characters
RIGHT(Customer[Name], 2)
LEN → Length of text
LEN(Customer[Name])
swipe
SEARCH → Finds position of text
SEARCH("an", Customer[Name])
UPPER → Converts to uppercase
UPPER(Customer[Name])
LOWER → Converts to lowercase
LOWER(Customer[Name])
PROPER → Capitalizes first letter
PROPER(Customer[Name])
swipe
🔹 Date & Time Functions
TODAY → Current date
TODAY()
NOW → Current date & time
NOW()
YEAR → Extracts year
YEAR(Order[Date])
MONTH → Extracts month
MONTH(Order[Date])
DAY → Extracts day
DAY(Order[Date])
DATEDIFF → Difference between dates
DATEDIFF(Order[StartDate], Order[EndDate],
DAY)
EOMONTH → End of month date
EOMONTH(Order[Date], 1)
WEEKDAY → Day of week number
WEEKDAY(Order[Date])
swipe
🔹 Filter & Context Functions
CALCULATE → Applies filters to a calculation
CALCULATE(SUM(Sales[Amount]),
Sales[Region]="West")
FILTER → Creates filtered table
FILTER(Sales, Sales[Amount] > 100)
ALL → Ignores all filters
ALL(Sales)
ALLEXCEPT → Ignores all filters except chosen
column
ALLEXCEPT(Sales, Sales[Region])
REMOVEFILTERS → Clears filters
REMOVEFILTERS(Sales[Product])
swipe
🔹 Time Intelligence
TOTALYTD → Year-to-date calculation
TOTALYTD(SUM(Sales[Amount]), Dates[Date])
SAMEPERIODLASTYEAR → Compare with last
year
SAMEPERIODLASTYEAR(Dates[Date])
PARALLELPERIOD → Shift period back/forward
PARALLELPERIOD(Dates[Date], -1, MONTH)
DATEADD → Move date context
DATEADD(Dates[Date], -1, MONTH)
PREVIOUSMONTH → Previous month values
PREVIOUSMONTH(Dates[Date])
NEXTYEAR → Next year values
NEXTYEAR(Dates[Date])
🔹 Iterator Functions
SUMX → Row-by-row sum
SUMX(Sales, Sales[Quantity] * Sales[Price])
AVERAGEX → Row-by-row average
AVERAGEX(Sales, Sales[Profit])
MAXX → Row-by-row max
MAXX(Sales, Sales[Discount])
MINX → Row-by-row min
MINX(Sales, Sales[Discount])
COUNTX → Row-by-row count
COUNTX(Sales, Sales[Product])
swipe
🔹 Ranking Functions
RANKX → Ranks rows based on expression
RANKX(ALL(Sales), SUM(Sales[Amount]), , DESC)
🔹 Variables
VAR + RETURN → Store intermediate results
Sales in Thousands =
VAR TotalSales = SUM(Sales[Amount])
RETURN TotalSales / 1000
swipe
🔹 Evaluation Functions
HASONEVALUE → True if only one value
selected
HASONEVALUE(Product[Category])
SELECTEDVALUE → Returns selected value or
default
SELECTEDVALUE(Product[Category], "Multiple")
VALUES → Distinct values in current context
VALUES(Product[Category])
DISTINCT → Unique values from column
DISTINCT(Sales[Product])
swipe
📝 Use measures instead of calculated columns
whenever possible — they’re faster and more efficient.
⚡ Always use VAR for cleaner, easier-to-read DAX
formulas.
🔍 Master CALCULATE, FILTER, and ALL — they are the
backbone of advanced DAX.
📊 Remember: DAX results depend on context — filters
and visuals change everything.
🚀 Break complex formulas into smaller parts and test
step by step.
Key Takeaways
Start every DAX formula with =
Always use table[column] format
Remember: CALCULATE = Power
Use iterators (X functions) for row-by-row logic
Time Intelligence helps with YTD, MTD, YoY