<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=612681139262614&amp;ev=PageView&amp;noscript=1">
Skip to content

Need help? Talk to an expert: phone(904) 638-5743

Quick DAX References

Scroll Down To View The DAX Cheat Sheet This DAX worksheet is crafted to facilitate the creation of DAX (Data Analysis Expressions) calculations for enhanced data analysis. It provides a structured framework and guidance for formulating DAX measures and functions. The worksheet includes dedicated sections for defining key metrics, specifying filters and conditions, and organizing the logic of calculations.

Download the DAX Cheat Sheet
Start Here:
DAX Beginner Tutorial Video The best interactive video to start when learning DAX. Learn the DAX fundamentals as well as topics like row context, the CALCULATE function and context transition.
On-Demand Learning DAX Courses While not all free, we have a number of courses focused on DAX in our On-Demand Learning library.
Dashboard In A Day Free Recordings Click Free Trial for a forever free Dashboard in a Day class amongst other courses.
Download Our Free DAX Guidebook Get a breakdown of how you can use DAX to get the most out of your data.
Get Started With Power BI For Free Create rich, interactive reports with visual analytics at your fingertips—for free. Once there, click Try Power BI For Free and use your credentials.
Power BI Licensing See the present pricing for Power BI

DAX Cheat Sheet

Problem Calculation Expression
Total Sales Calculation Calculated measure using SUM to aggregate a column. 
Total Sales = SUM('TableName'[SalesAmount])
Total Cost Calculation Calculated measure using SUM to aggregate a column. 
Total Cost = SUM('TableName'[Cost])
Profit Calculation Calculated measure using two previously created calculated measures to determine profit. 
Profit = [Total Sales] - [Total Cost]
Profit Margin Calculated measure using two previously created calculated measures to determine profit margin, the DIVIDE function is used to perform the division. 
Profit Margin = DIVIDE( [Profit], [Total Sales])
Transaction Count Calculated measure that returns a count of all rows in a table, ultimately, many times this simple calculation is used to return transaction counts. 
Transactions = COUNTROWS('Table')
Related Table Count Returns the total rows in a related table. For example, total transactions by Product. 
Transactions = COUNTROWS(RELATEDTABLE('TABLE'))

Month To Date Sales

Problem Calculation Expression
MTD Sales Calculates Total Sales for all days in the current month up to the maximum day in the selection. 
MTD Sales = TOTALMTD( [Total Sales], 'DateTable'[DateColumn] )
3 Month Average Calculation 3 Month Average = 
CALCULATE(
  AVERAGEX(
    SUMMARIZE('Date', 'Date'[Month]), 
    [Measure]),
  DATESINPERIOD(
    'Date'[Date],
    LASTDATE('Date'[Date]),
    -3
    MONTH)
)

New Category for Conditional / Logical Operations

 
Problem Calculation Expression
IF Condition 
IF Condition =
IF( < logical_test >, <value_if_true>, <value_if_false>)
SWITCH Statement

The switch statement can be used when there are many logical tests that need to be performed.

SWITCH Statement =
SWITCH(
  <Column>,
 <Value>, <Result>,
 <Value>, <Result>
   <Else> )
AND Function

The AND function evaluates whether both given conditions are met. If both conditions are indeed met, it yields a TRUE result; if not, it results in FALSE.

AND Function =
AND( <logical1>, <logical2> )
OR Function

The OR function evaluates whether either given condition is met. If either condition is met, it yields a TRUE result; if not, it results in FALSE.

OR Function =
OR( <logical1>, <logical2> )

Opening and Closing Balance

 
Problem Calculation Expression
OPENINGBALANCEYEAR()

Opening Balance can be achieved using any of the three existing built-in functions which will allow you to return the closing balance for Month, Quarter or Year.

Opening Balance (Year, Month, Quarter) =

OPENINGBALANCEYEAR(
  [Measure],
  'Date'[Date] )
CLOSINGBALANCEYEAR()

Closing Balance can be achieved using any of the three existing built-in functions which will allow you to return the closing balance for Month, Quarter or Year.

Closing Balance (Year, Month, Quarter) =

CLOSINGBALANCEYEAR(
  [Total Sales],
  'Date'[Date] )

Lookupvalue / Vlookup / Related

 
Problem Calculation Expression
Vlookup with no relationship

The lookupvalue function will return a value from another table when a relationship does not exist in the data model.

Return column from another table (LookupValue) =
LOOKUPVALUE(
    'DestTable'[ColumnToReturn],
    'DestTable'[Key],
    'SourceTable'[Key])
Vlookup with relationship

The Related function will return a value from another table leveraging existing relationships in the data model.

Return column from another table (Related) =
RELATED( 'Table'[ColumnToReturn] )

Year To Date Sales

 
Problem Calculation Expression
YTD Sales Calculates Total Sales for all days in the year up to the maximum day in the selection. 
YTD Sales = TOTALYTD( [Total Sales], 'DateTable'[DateColumn] )
YTD Sales (Fiscal Calendar) This calculation uses an optional third parameter specifying the fiscal year end date. 
YTD Sales = TOTALYTD( [Total Sales], 'DateTable'[DateColumn], "05/31" )

Prior Year Sales

 
Problem Calculation Expression
Prior Year Profit 
Prior Year Profit = CALCULATE ( [Profit], SAMEPERIODLASTYEAR'DateTable'[DateColumn] )
Year over Year Profit Calculated measure using two previously created calculated measures to determine YoY profit. 
YoY Profit = [Profit] - [Prior Year Profit]
Last Year YTD Sales 
Last YTD Sales = CALCULATE ( [YTD Sales], SAMEPERIODLASTYEAR('DateTable'[DateColumn] ) )
Total Sales for all Countries This calculation uses calculate to return all countries in the calculation regardless of the filter context. 
Total Sales All Countries = CALCULATE ( [Total Sales], ALL('Geography Table'[Country] ) )
Percent of Total Calculation This calculation uses two measures previously created to create a percent of total calculation. 
Percent of Total = DIVIDE([Total Sales], [Total Sales All Countries])

Moving Totals

 
Problem Calculation Expression
Rolling 12 Month Sales Calculated measure that returns a rolling 12 months total for Profit. 
Rolling 12 Months Profit = 
    CALCULATE ( [Profit],
              DATESBETWEEN('DateTable'[DateColumn] ,
              NEXTDAY(
                     SAMEPERIODLASTYEAR(
                               LASTDATE(DateTable'[DateColumn] ))),
              LASTDATE('DateTable'[DateColumn])))
7 Day Moving Total 
7-Day Moving Total =
CALCULATE(
    [Measure],
    DATESINPERIOD(
        'Date'[Date],
        LASTDATE('Date'[Date]),
        -7,
        DAY) )
Country Rank Calculated measure to rank a specific column in a table by a measure. In this measure Country from the geography table is being ranked by the measure [Total Sales]. 
Country Rank = RANKX( ALL ('GeographyTable'[Country]), [Total Sales],,,Skip)