DAX Cheat Sheet – Essentials for Power BI
With our project this week being in Power BI, I thought it was the perfect opportunity to create a DAX Cheat Sheet!
DAX (Data Analysis Expressions) is the formula language used in Power BI for creating calculated columns, measures, and calculated tables. There are a lot of different functions, but most analytical work relies on a core set of functions which I will cover.
1. Syntax & Operators:
Operators form the basis of DAX expressions. They allow you to perform calculations, comparisons, evaluations, and text concatenation. Understanding these is key before moving into more advanced functions.
Numerical Operators
- + Addition
- - Subtraction
- * Multiplication
/ Division
^ Exponent
Comparison Operators
= Equal
== Strict equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
Logical Operators
&& AND
|| OR
IN Logical OR given a list of values
Text Operator
& Concatenate text
Table Reference
' Used to reference table names containing spaces
2. Aggregation Functions
Aggregation functions summarise column-level data. They are most commonly used inside measures and are foundational for KPIs, performance metrics, and summary reporting.
SUM: Calculates the sum of values in a column.
AVERAGE: Calculates the average of values in a column.
MIN: Returns the minimum value in a column.
MAX: Returns the maximum value in a column.
COUNT: Counts non-blank values in a column.
COUNTBLANK: Counts blank values.
DISTINCTCOUNT: Counts unique values.
MEDIAN: Returns the median value.
3. Text Functions
Text functions are used for string manipulation, formatting, extraction, and cleaning. They are particularly useful in calculated columns when preparing data for modelling or categorisation.
CONCATENATE: Combines two text values.
LEFT: Returns characters from the left.
RIGHT: Returns characters from the right.
MID: Returns characters from a specified position.
LEN: Returns the length of a string.
LOWER: Converts text to lowercase.
UPPER: Converts text to uppercase.
TRIM: Removes leading and trailing spaces.
SUBSTITUTE: Replaces text within a string.
FIND: Returns the starting position of text within another string.
Although CONCATENATE exists, you can also use the ‘&’ operator for readability.
4. Logical Functions
Logical functions allow you to create formulas that respond to conditions. They evaluate whether a statement is true or false and then return different results depending on the outcome. Rather than returning a single fixed value, they enable your calculations to adapt based on the logic you define.
IF: Returns one value if true, another if false.
AND: Returns TRUE if all conditions are true.
OR: Returns TRUE if at least one condition is true.
NOT: Reverses a logical value.
Logical functions are commonly nested within measures and used inside FILTER expressions.
5. Date & Time Functions
Date and time functions allow you to work with temporal data in a structured and meaningful way. They extract specific components such as year, month, or day, or generate dynamic values like the current date and time. Rather than treating dates as static values, these functions enable your calculations to adapt based on time logic and reporting periods.
TODAY: Returns the current date.
NOW: Returns the current date and time.
DATE: Creates a date value.
TIME: Creates a time value.
YEAR: Returns the year.
MONTH: Returns the month.
DAY: Returns the day.
HOUR: Returns the hour.
MINUTE: Returns the minute.
SECOND: Returns the second.
DATEDIFF: Returns the difference between two dates.
These are used in calculated columns to derive fields such as Year or Month.
6. Table Functions
Table functions work directly with tables rather than individual values. They adjust how data is filtered or structured before a calculation is performed. Rather than simply aggregating data, these functions allow you to control how data is filtered, grouped, or evaluated.
FILTER: Returns a filtered table.
ALL: Removes filters from a table or column.
VALUES: Returns unique values.
SUMMARIZE: Creates a grouped summary table.
FILTER and ALL are particularly important in measure logic, especially when controlling slicer behaviour.
7. Time Intelligence Functions
Time intelligence functions are designed for cumulative, comparative, and period based analysis. They depend on a properly structured and marked Date table within your data model to function correctly. Rather than manually calculating time comparisons, these functions allow you to analyse performance across defined reporting periods such as year to date or the same period last year.
DATESYTD: Returns year-to-date dates.
TOTALYTD: Calculates year-to-date totals.
SAMEPERIODLASTYEAR: Returns the equivalent period last year.
DATESBETWEEN: Returns a specified date range.
These functions are essential for financial reporting, YoY growth calculations, and trend comparisons.
8. Information Functions
Information functions are used for validation and writing robust DAX measures. They allow you to check the type or state of a value before performing a calculation. Rather than assuming your data is clean and consistent, these functions help you handle blanks, unexpected data types, or potential errors more safely.
ISBLANK: Returns TRUE if blank.
ISNUMBER: Returns TRUE if numeric.
ISTEXT: Returns TRUE if text.
ISDATE: Returns TRUE if date.
This cheat sheet focuses on essential DAX functions used in everyday Power BI development. It is not an exhaustive list of all available expressions, but it provides a practical working reference covering aggregation, logic, text handling, dates, table manipulation, statistical analysis, and time intelligence. Now go and enjoy writing your own DAX expressions!