Power BI DAX Functions: Cheat Sheet with Examples

Quick Overview

• Power BI DAX functions help calculate sales totals, profit margins, unique customers, filtered results, and year-over-year growth in Power BI reports.
• Functions such as SUM, AVERAGE, COUNTROWS, DISTINCTCOUNT, and DIVIDE handle common calculations, while SUMX performs row-by-row calculations before adding the results.
• CALCULATE, FILTER, and REMOVEFILTERS help control filter context and create measures such as category-wise sales and percentage of total.
• TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD support time-based analysis, including year-to-date sales and comparisons with previous periods.
• This guide covers the most useful Power BI DAX functions, basic aggregation and calculations, filtering and dynamic measures, date and time intelligence, differences between commonly confused functions, and common mistakes to avoid.

Strengthen Power BI skills and learn to build interactive dashboards, analyze data, and apply DAX functions through practical training with Microsoft Power BI Training. 

What are the most useful Power BI DAX functions?

Power BI DAX functions are built-in functions which help in carrying out calculations on the data in a model. They are capable of calculating totals, applying conditions, altering the filter context, and supporting time-based analysis.

The following is a brief reference table of useful functions for everyday Power BI reports.

Function	Purpose	Example use
SUM	Adds values in a column	Calculate total sales
AVERAGE	Calculates the average	Find average sales
COUNTROWS	Counts table rows	Count sales records
DISTINCTCOUNT	Counts unique values	Count unique customers
DIVIDE	Divides two values safely	Calculate profit margin
SUMX	Evaluates an expression for each row and adds the results	Calculate revenue from quantity and price
CALCULATE	Evaluates a calculation under modified filters	Calculate sales for a category
FILTER	Returns rows that meet a condition	Find high-value sales
IF	Checks a condition and returns a result	Label profit or loss
SWITCH	Evaluates multiple conditions or matching values	Group sales into performance bands
RELATED	Retrieves a value from a related table	Get a product category
SELECTEDVALUE	Returns a value when one distinct value is selected	Display a selected category
REMOVEFILTERS	Removes specified filters	Calculate a category's share of total sales
TOTALYTD	Calculates year-to-date values	Track sales so far this year
SAMEPERIODLASTYEAR	Returns the corresponding dates from the previous year	Compare yearly sales

Also Read: Features of Power BI

What are the basic DAX functions for aggregation and calculations?

These are the basic Power BI DAX functions very much like SUM and AVERAGE in Excel, which are used in measures, so their result will change as the filters and slicers on the report page change.

1. SUM, AVERAGE, MIN and MAX

These four functions perform basic calculations on a column. SUM adds up the values in a column. AVERAGE gives the mean. MIN and MAX give the smallest and largest values respectively. They're the trio of functions that answer the first questions in any report: what's the total, what's the average, what's the best and what's the worst.

 

Total Sales = SUM(Sales[SalesAmount])   Average Sales = AVERAGE(Sales[SalesAmount])   Highest Sale = MAX(Sales[SalesAmount])   Lowest Sale = MIN(Sales[SalesAmount])

For example, a sales report can use these measures to show total revenue, average transaction value, and the highest and lowest sales amounts. The results can change based on the filters applied to the report.

2. COUNTROWS and DISTINCTCOUNT

COUNTROWS counts the rows in a table and is therefore ideal for counting total orders. DISTINCTCOUNT counts only the unique values in a column. A customer who has placed five orders will be counted five times by COUNTROWS and only once by DISTINCTCOUNT.

Total Sales Records = COUNTROWS(Sales)   Unique Customers =     DISTINCTCOUNT(Sales[CustomerID])

3. DIVIDE and SUMX

Use DIVIDE when one value needs to be divided by another. It handles zero or blank denominators more safely than ordinary division.

Profit Margin =     DIVIDE([Total Profit], [Total Sales], 0)

This measure divides total profit by total sales. If total sales is zero or blank, the formula returns zero.

SUMX is useful when a calculation must be performed for every row before the results are added.

Calculated Revenue =     SUMX(         Sales,         Sales[Quantity] * Sales[UnitPrice]     )

For each row, Power BI multiplies quantity by unit price and then adds the results. This is useful when revenue is not already stored as a column in the source data.

Also Read: Power BI Skills 

Which DAX functions help filter data and create dynamic measures?

Some DAX calculations need to respond to report filters or apply specific conditions. Functions such as CALCULATE, FILTER, REMOVEFILTERS, IF, and SWITCH help create these measures.

1. CALCULATE, FILTER and REMOVEFILTERS

CALCULATE is the most important function in DAX. It takes a measure and re-evaluates it under a new filter context.

Electronics Sales =     CALCULATE(         [Total Sales],         Product[Category] = "Electronics"     )

This measure calculates sales for the Electronics category while retaining other applicable report filters.

FILTER returns a table of rows that meet a condition. It is useful when your logic is more complex than a simple column match.

For example, a measure that calculates sales from transactions above a certain amount can use FILTER to identify qualifying rows.

High Value Sales =     CALCULATE(         [Total Sales],         FILTER(             Sales,             Sales[SalesAmount] > 1000         )     )

This example assumes that SalesAmount contains the amount for each sales row.

REMOVEFILTERS removes the filters, which is useful for calculating the percentage of a larger total.

Sales % of Total =     DIVIDE(         [Total Sales],         CALCULATE(             [Total Sales],             REMOVEFILTERS(Product[Category])         )     )

2. IF, SWITCH and SELECTEDVALUE

These functions help make reports more flexible and easier to understand. IF handles a simple two way decision. SWITCH is cleaner when you have multiple outcomes and you can use TRUE() as the first argument to have it test ranges. SELECTEDVALUE returns the selected item where exactly one is selected and that makes it ideal for dynamic report titles.

Profit Status =     IF([Total Profit] >= 0, "Profit", "Loss")   Sales Band =     SWITCH(         TRUE(),         [Total Sales] >= 100000, "High",         [Total Sales] >= 50000, "Medium",         "Low"     )   Selected Category =     SELECTEDVALUE(Product[Category], "Multiple or none")

Also Read: Top 20 Benefits of Power BI you Should Know in 2026 

Which DAX functions are used for date and time intelligence?

Time intelligence is one of the primary reasons people go and learn Power BI DAX functions in the first instance. Business users are always looking to see year to date numbers or to compare this year with last year and these functions enable you to do that with very little code. The one rule that applies to all of them is that you need a proper date table in the model i.e. one that's marked as a date table.

1. TOTALYTD and DATESYTD

Year to date reporting is the sum of the values from the start of the year up to the most recent date in the current filter. TOTALYTD does this in one go. DATESYTD returns the list of dates and CALCULATE then uses that list which is useful when you have additional filters required.

Sales YTD =     TOTALYTD(         [Total Sales],         'Date'[Date]     )   Sales YTD (DATESYTD) =     CALCULATE(         [Total Sales],         DATESYTD('Date'[Date])     )

Both give the same answer in this case. TOTALYTD is the shorter of the two so that's the one most people start with.

2. SAMEPERIODLASTYEAR and DATEADD

SAMEPERIODLASTYEAR simply shifts the current date selection back by one year. DATEADD is more flexible as it can shift the date by days, months, quarters or years. Once you have the value for last year the year on year change is just a simple division.

Sales Last Year =     CALCULATE(         [Total Sales],         SAMEPERIODLASTYEAR('Date'[Date])     )

To calculate the percentage change from the previous year, use:

Sales YoY Change % =     DIVIDE(         [Total Sales] - [Sales Last Year],         [Sales Last Year]     )

Build a strong foundation in analytics with the PG Certificate in Data Analytics and Applied AI, covering Power BI, SQL, Python, and AI-powered analytics for real-world business problems. 

What is the difference between commonly confused DAX functions?

Some Power BI DAX functions appear similar but serve different purposes. Choosing the right one helps prevent incorrect results and makes formulas easier to maintain.

Functions	Key difference	When to use
SUM vs. SUMX	SUM adds a column; SUMX evaluates an expression for each row and adds the results	Use SUM for existing totals and SUMX for calculations such as quantity × price
COUNTROWS vs. DISTINCTCOUNT	COUNTROWS counts rows; DISTINCTCOUNT counts unique values in a column	Use the first for records and the second for unique customers
CALCULATE vs. FILTER	CALCULATE evaluates an expression under modified filters; FILTER returns a filtered table	Use them for filtered measures and more complex row conditions
IF vs. SWITCH	IF checks a condition; SWITCH handles multiple branches or matching values	Use IF for simple decisions and SWITCH for several categories
ALL vs. REMOVEFILTERS	ALL can return a table or act as a filter modifier; REMOVEFILTERS is specifically used to clear filters	Use ALL when a table expression is needed and REMOVEFILTERS when the goal is to remove filters

Also Read: Power BI with AI vs Traditional Data Analytics: What’s Different? 

What are the common mistakes to avoid when using DAX functions?

Using DAX incorrectly can lead to unexpected totals, blank results, or inaccurate percentages. Keep these common mistakes in mind when writing formulas.

1. Using SUM instead of SUMX

SUM will only work on a single column. If revenue is a function of quantity and price which are stored in separate columns then SUM won't work as it can't multiply them together first. SUMX will do the calculation row by row and then sum the results which will give you the correct total.

2. Confusing COUNTROWS with DISTINCTCOUNT

A report which shows "customers" but uses COUNTROWS will count every order rather than every customer. The number will appear larger than it should. Using DISTINCTCOUNT on the customer ID column will correct this. Choose the function based on whether the requirement is to count records or distinct values.

3. Ignoring filter context in CALCULATE

A measure doesn't return a single value. It varies with every slicer, every row and every column in the visual. CALCULATE can override that context but only if you're very careful about the filters you use. It's always worth testing the measure in a simple table first to catch any surprises.

4. Dividing without handling zero values

If you divide numbers without checking for zero values first, you can run into errors. Division by zero is not allowed in math. If your code tries to do this, it will crash or give wrong results. Always check that the divisor is not zero before dividing. This helps keep your program running smoothly and prevents mistakes.

5. Using time-intelligence functions without a proper date table

TOTALYTD, SAMEPERIODLASTYEAR and DATEADD all require a continuous date table with no gaps. If you don't have one then you can end up with incorrect results or blank results. The safe way to set this up is to have a separate date table (marked as a date table) and link it to the fact table.

Take your AI skills further with the Applied Agentic AI Certification Course and learn to build intelligent AI applications through hands-on projects.

Conclusion

Power BI DAX functions make it easier to turn raw data into meaningful insights, from calculating total sales and profit margins to tracking year-to-date performance. Choosing the right function, whether SUMX for row-by-row calculations or CALCULATE for changing filter context, helps produce accurate results.

Beginners should start with basic aggregation functions before moving to conditional logic and time intelligence. Practicing these formulas with real datasets is the best way to understand how DAX works in different reporting situations.

Have A Query? Get in Touch With Our Customer Support | upGrad KnowledgeHut