Power BI DAX Cheat Sheet: Your Ultimate Guide

What is DAX?

DAX, or Data Analysis Expressions, is a formula language used in Power BI, Power Pivot, and SQL Server Analysis Services (SSAS). DAX enables you to perform data analysis, create calculated columns, measures, and custom queries that help users gain valuable insights from data. DAX is very similar to Excel formulas but with more advanced functionality suited for handling complex data models.

Structure of DAX

In Power BI, DAX formulas generally consist of the following elements:

• Functions – DAX comes with a wide variety of built-in functions.
• Operators – These are used for performing arithmetic, logical, and comparison operations.
• Values – These could be constants, text strings, or cell references.
• Expressions – A combination of functions, values, and operators to create formulas.

Understanding how these elements interact will make it easier to create more complex formulas and improve your data analysis capabilities.

1. Basic DAX Functions

Let’s start with some essential DAX functions. These are commonly used and should be part of every Power BI user’s toolkit.

SUM

The SUM function adds up all the numbers in a column.

SUM(Column)

Example:

Total Sales = SUM(Sales[Amount])

AVERAGE

The AVERAGE function calculates the average of all the numbers in a column.

AVERAGE(Column)

Example:

Average Sales = AVERAGE(Sales[Amount])

COUNT

The COUNT function counts the number of values in a column, excluding blanks.

COUNT(Column)

Example:

Count of Customers = COUNT(Customers[CustomerID])

COUNTA

The COUNTA function counts the number of non-empty values in a column (including text, numbers, or logical values).

COUNTA(Column)

Example:

Count of Products = COUNTA(Products[ProductName])

IF

The IF function is used to perform conditional logic in your DAX formulas.

IF(Condition, TrueResult, FalseResult)

Example:

Sales Status = IF(Sales[Amount] > 1000, "High", "Low")

DISTINCTCOUNT

This function counts the number of distinct (unique) values in a column.

DISTINCTCOUNT(Column)

Example:

Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

2. Time Intelligence Functions

Time intelligence functions allow you to work with date-related calculations in Power BI.

TOTALYTD (Year-To-Date)

The TOTALYTD function calculates the total value from the beginning of the year to the current date.

TOTALYTD(Expression, Dates)

Example:

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[Date])

SAMEPERIODLASTYEAR

This function compares the same period in the previous year.

SAMEPERIODLASTYEAR(Dates)

Example:

Sales LY = SAMEPERIODLASTYEAR(Sales[Date])

DATEADD

The DATEADD function returns a table that shifts the date by a specified number of intervals.

DATEADD(Dates, NumberOfIntervals, Interval)

Example:

Sales Previous Month = DATEADD(Sales[Date], -1, MONTH)

PARALLELPERIOD

This function returns a table that shifts a date range by a specified number of intervals, similar to DATEADD.

PARALLELPERIOD(Dates, NumberOfIntervals, Interval)

Example:

Sales 3 Months Ago = PARALLELPERIOD(Sales[Date], -3, MONTH)

3. Filter Functions

Filter functions in DAX allow you to apply filters to your calculations. They are essential for advanced data analysis and reports.

CALCULATE

The CALCULATE function evaluates an expression within the context of modified filters.

CALCULATE(Expression, Filter1, Filter2, ...)

Example:

Sales 2024 = CALCULATE(SUM(Sales[Amount]), Sales[Year] = 2024)

FILTER

The FILTER function returns a table that represents a subset of data based on a condition.

FILTER(Table, Condition)

Example:

Filtered Sales = FILTER(Sales, Sales[Amount] > 1000)

ALL

The ALL function removes all filters from a table or column.

ALL(TableOrColumn)

Example:

Total Sales Without Filter = CALCULATE(SUM(Sales[Amount]), ALL(Sales))

RELATED

The RELATED function is used to fetch related data from another table.

RELATED(Column)

Example:

Customer Name = RELATED(Customers[CustomerName])

4. Logical Functions

Logical functions help you control the flow of your calculations by allowing you to combine conditions.

AND

The AND function returns TRUE if both conditions are true.

AND(Condition1, Condition2)

Example:

High Value Sales = IF(AND(Sales[Amount] > 1000, Sales[Region] = "North"), "High", "Low")

OR

The OR function returns TRUE if at least one of the conditions is true.

OR(Condition1, Condition2)

Example:

Top Sales = IF(OR(Sales[Amount] > 5000, Sales[Region] = "East"), "Top Seller", "Standard")

NOT

The NOT function negates a condition, making it return the opposite result.

NOT(Condition)

Example:

Not in North Region = IF(NOT(Sales[Region] = "North"), "Not North", "North")

5. Text Functions

Text functions are used to manipulate and format text values in Power BI.

CONCATENATE

The CONCATENATE function combines two text strings into one.

CONCATENATE(Text1, Text2)

Example:

Full Name = CONCATENATE(Employee[FirstName], Employee[LastName])

LEFT and RIGHT

The LEFT and RIGHT functions return the leftmost or rightmost characters of a string.

LEFT(Text, NumberOfCharacters)

RIGHT(Text, NumberOfCharacters)

Example:

First 3 Characters = LEFT(Customer[Name], 3)

Last 3 Characters = RIGHT(Customer[Name], 3)

6. Advanced DAX Functions

Now let’s move on to more advanced DAX functions that help with complex data analysis.

RANKX

The RANKX function ranks a set of values based on a specified expression.

RANKX(Table, Expression, [Value], [Order], [Ties])

Example:

Rank of Sales = RANKX(ALL(Sales), Sales[Amount], , DESC)

LOOKUPVALUE

The LOOKUPVALUE function returns a value based on a search criterion in another column.

LOOKUPVALUE(ColumnToReturn, SearchColumn, SearchValue)

Example:

Customer Email = LOOKUPVALUE(Customers[Email], Customers[CustomerID], Sales[CustomerID])

Conclusion

Mastering DAX in Power BI opens up a world of possibilities for data analysis and reporting. With the functions outlined in this cheat sheet, you now have the tools to create powerful formulas, perform complex calculations, and build insightful reports that provide real value to your organization.

Remember, practice is key to mastering DAX. Start simple, and gradually tackle more complex formulas as you become comfortable with the language. Whether you’re building calculated columns, measures, or custom time intelligence calculations, DAX is an indispensable tool for making the most of Power BI.

Happy analyzing!