Top everyday DAX functions

tl;dr DAX has probably hundreds of functions, however, several of them stand out. You will probably use them in every project. Normally I would add them here, but below there's table of contents.

Disclaimer: If you don't see your favorite function here, it doesn't mean it's bad, this article covers my top pick of really core ones.

Criteria explanation

I won’t dive into DAX (Data Analysis Expressions) concepts or detailed explanations in this article. Instead, I’ll go straight to the point with a list of the most useful functions. I’ll briefly introduce each one and provide real-life examples. To keep things organized, I’ve grouped them into categories—starting with the most essential ones, as the order matters.

Table of contents (with anchors)

Basic Aggregators

• SUM

• AVERAGE

• COUNTROWS

• DISTINCTCOUNT

• COUNT

• MIN/MAX

Filter Functions

• CALCULATE

• REMOVEFILTERS

• ALL

• ALLSELECTED

• USERELATIONSHIP

• KEEPFILTERS

• SELECTEDVALUE

Iterative Functions

• SUMX, AVERAGEX, COUNTX

• VALUES

• SUMMARIZE

• CONCATENATEX

Time Intelligence Functions

• TOTALMTD, TOTALYTD, TOTALQTD

• DATESINPERIOD

• SAMEPERIODLASTYEAR

• PARALLELPERIOD

• DATEADD

Other Core Functions

• SWITCH

• ISBLANK

• IF

• USERPRINCIPALNAME

• BLANK

• DIVIDE

• MAXX/MINX

Basic aggregators

You can't go wrong with these, and I guarantee you will use at least one of them in every single report.

SUM

SUM([column])
Adds all the numbers in a column.

It just adds up the numbers in column. No witchcraft here.

AVERAGE

AVERAGE([column])
Returns the average (arithmetic mean) of all the numbers in a column.

Similar to SUM, very common, especially with iterators (but about that later).

COUNTROWS

COUNTROWS(<table>)
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

Most of you may be surprised why is COUNTROWS so up, but based on how I model my reports, I usually want to count the whole table instead of just a single column.

DISTINCTCOUNT

DISTINCTCOUNT([column])
Counts the number of distinct values in a column.

Getting information about distinct values as a number is quite underrated, that's why this one is still on the top.

COUNT

COUNT([column])
Counts the number of rows in the specified column that contain non-blank values.

Finally, here's COUNT. Still a very valuable one, but you gotta keep in mind that this will skip blank/null values, which may sometimes confuse you and you can be better off with COUNTROWS or DISTINCTCOUNT, especially if you just want to count the number of IDs in a table.

MIN/MAX

MIN/MAX([column]); MIN/MAX(<expression1>, <expression2>)
Returns the smallest/biggest value in a column, or between two scalar expressions.

Last but not least, MIN and MAX can provide crucial values that can later be used in filter expressions, aggregations, or even to help with the current context—often eliminating the need for a lengthy SELECTEDVALUE function.

Filter functions

The second most important group consists of filter functions, which allow you to filter aggregated values based on an expression or modify the context in which those values are evaluated.

CALCULATE

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
Evaluates an expression in a modified filter context.

For me, CALCULATE is the #1 function. If your model is well-structured, you can create something truly elegant with it—almost like a piece of art. It’s simple and powerful, as long as you don’t overthink it (or underthink it?)

CALCULATE provides an elegant way to apply filters, but it works best if you follow certain rules: filter columns, not tables, and try to avoid using FILTER inside the filter expression of CALCULATE.

The most straightforward way to use CALCULATE is by writing 'Table'[Column] = "xx", but that’s just scratching the surface. One of its most powerful features is the ability to modify the filter context using a wide range of additional functions (I’ll cover some of them). This allows you to activate inactive relationships, "ignore" filters from inside or outside a query, or leverage powerful Time Intelligence functions.

REMOVEFILTERS

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])
Clear filters from the specified tables or columns.

Mostly used in CALCULATE filter expressions, REMOVEFILTERS removes filters—simple, right? This function is particularly powerful when you want to ensure that certain calculations remain unaffected by specific filters. You can use it to clear filters from an entire table or just a particular column.

This comes in handy when calculating a portion of a whole. For instance, if you have a dataset with product categories and colors, and you want to determine what percentage each color contributes within its category, you can ignore the color filter as a base and then perform the rest of the calculation. This approach helps derive the percentage contribution of each color within a category.

REMOVEFILTERS is similar to the ALL function. However, when used inside CALCULATE, REMOVEFILTERS is generally recommended, as it tends to perform better and avoids potential side effects.

ALL

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

Mostly used in CALCULATE’s filter expressions, this bad boy can sometimes be a real head-scratcher—especially when you start learning about ALLSELECTED.

But here’s the key to understanding it: whatever you put inside ALL, you’ll get all rows of that. Or, thinking about it differently—you’re ignoring all filters that could be applied to it.

If you want a truly alternative way to think about ALL, imagine it as that one uncle at family gatherings who has absolutely no filter—he’ll say anything, no matter what. Just like him, ALL ignores any filters and brings everything to the table.

ALLSELECTED

ALLSELECTED([<tableName> | <columnName>[, <columnName>[,…]]] )
Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Mostly used in CALCULATE’s filter expressions, ALLSELECTED works very similarly as ALL. The only difference is that you are cosplaying ALL just inside the query. But external filters will still affect you. Inside the query means inside visualization where you put the measure it. I think this deserves a picture:

ALLSELECTED example

The first table shows a regular SUM. The second table uses the same SUM, but with the ALLSELECTED modifier.

In the second visual (inside the query), individual rows are ignored—you can see that A is normally 100 and B is 200, but here, both display as 300. This happens because ALLSELECTED doesn’t respect the filter context at the row level inside the query.

However, the external query (the slicer on the right) still applies a filter to the visual. In this case, it filters A and B, meaning the external query is respected. As a result, for each selection, you get the aggregated value of both letters.

Since I love unhinged explanations, imagine ALLSELECTED as a group of teenagers. At home, they completely ignore whatever their mom tells them (internal filters). But the moment Rebecca from the other class says something (external filters), they pay full attention—because she’s the famous one, and obviously, you have to follow her trends.

USERELATIONSHIP

USERELATIONSHIP([columnName1],[columnName2])
Specifies the relationship to be used in a specific calculation as the one that exists between columnName1 and columnName2.

Technically a relationship function, but most commonly used in CALCULATE’s filter expressions.

This function is often overlooked at the beginner stage, but once you start using it, it can save you a ton of time in Power BI modeling. Instead of juggling multiple active relationships, you can focus on a single primary active relationship and create multiple inactive ones (often for date fields, but not limited to them).

With this approach, you can easily switch between relationships within a

measure, allowing you to display two different measures in the same visualization—each affected by a different date field (as long as you have a calendar table).

KEEPFILTERS

KEEPFILTERS(<expression>)
Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

Used in CALCULATE’s filter expressions, KEEPFILTERS wraps a filter expression and is primarily used to correct unwanted behaviour or address functions that might produce visually confusing results.

Let’s look at an example:

KEEPFILTERS example

In this example, sum_a is hardcoded to filter for the letter "A". Under standard behaviour, other letters (B, C, etc.) would be filtered by both "A" and their respective letter. Since this is logically impossible, the filter on "A" takes precedence, causing the measure to always return the sum of "A", making it appear as if the value is being repeated across all rows.

On the other hand, sum_a_keepsfilters applies the same filter but wraps it inside KEEPFILTERS. This ensures that the existing "row-level" filters are respected, meaning the measure will only return a value where the filter logically applies—showing a value only for "A" and keeping all other letters blank.

sum_a_keepsfilters = 
CALCULATE(
    [sum],
    KEEPFILTERS('dimension'[dimension] = "a")
)

Without using the KEEPFILTERS, formula would look like this:

sum_a = 
CALCULATE(
    [sum],
    'dimension'[dimension] = "a"
)

SELECTEDVALUE

SELECTEDVALUE([columnName],[, <alternateResult>])
Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

SELECTEDVALUE is often used in expressions where you need to evaluate the current value against other values. It’s also commonly used as a variable within measures.

Keep in mind that if there’s more than one value for the column in the given context, SELECTEDVALUE will return BLANK—unless you specify an alternative result.

Iterative functions

Iterators require a table and an expression that is evaluated for each row, introducing row context to measures.

Finding the right balance is key—when used correctly, iterators can eliminate the need for physical columns in your model, improving efficiency. However, if used incorrectly, they can significantly slow down your report due to the row-by-row computation.

SUMX, AVERAGEX, COUNTX

SUMX,AVERAGEX,COUNTX(<table>,<expression>)
Returns the sum/average/count of an expression evaluated for each row in a table.

Similar to their basic forms, these functions perform sum, average, or count operations. However, since they are iterators, their syntax differs, and they require specific use cases.

You’ll use them when you need to first calculate an expression for each row and then sum, average, or count the results. For example, if you want to average total sales but only have quantity and unit price columns, you can write:

AVERAGEX('table', 'table'[quantity] * 'table'[unitPrice])

This returns the correct value without needing a physical column.

As you progress, combining iterators with functions like VALUES or SUMMARIZE becomes a powerful technique—allowing you to calculate, for example, average values aggregated by months or other dimensions.

VALUES

VALUES(<TableNameOrColumnName>)
When the input parameter is a column name, returns a one-column table that contains the distinct values from the specified column. Duplicate values are removed and only unique values are returned

I included VALUES with iterators, even though it’s not technically an iterator itself. However, as mentioned, you’ll often use it to iterate over distinct values and perform calculations for each one.

VALUES is strictly a table function, meaning you cannot use it directly in a measure since it returns a table, not a scalar value. However, you can combine it with CALCULATETABLE and other functions that modify filters, making it a powerful tool for controlling context in your calculations.

SUMMARIZE

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
Returns a summary table for the requested totals over a set of groups.

Similar to VALUES, this function is incredibly useful when combined with iterators, as it allows you to expand the grouping level when you need more than one column.

For example, if you want to group data by two dimensions and then calculate an average, SUMMARIZE is the way to go.

In real-world scenarios, it can be used to create a "pseudo" primary key when you need a unique identifier across multiple columns. You can then, for instance, count the distinct combinations of those columns for further analysis.

CONCATENATEX

CONCATENATEX(<table>, <expression>[, <delimiter> [, <orderBy_expression> [, <order>]]...])
Concatenates the result of an expression evaluated for each row in a table.

My favorite function when building string-based measures, whether for information panels showing filtered values or for displaying dynamic messages in a card when multiple values are present.

A real-life example: Together with Row-Level Security (RLS), you can use CONCATENATEX to inform users about their access. For instance, when they first open your report and aren’t sure what they are allowed to see, you can display a message listing the values they have access to within specific dimensions, helping them understand their data scope.

Time Intelligence functions

DAX offers a bunch of powerful built-in functions for calculating time-based metrics. The entire set of Time Intelligence functions acts as "sugar syntax," meaning you don’t have to write long CALCULATE functions to get results—you can simply reference these functions. If you're comparing year-over-year data or any other date-based comparisons, Time Intelligence functions are a must-have. Note that to utilize these, you gotta have a proper calendar.

TOTALMTD, TOTALYTD, TOTALQTD

TOTALMTD(<expression>,<dates>[,<filter>])
Evaluates the value of the expression for the month to date, in the current context.

As the name suggests, this function evaluates your expression for the current month, year, or quarter. Typically, you would use it with an existing measure as the expression.

These functions operate on a "to-date" level, meaning they calculate values from the first day of the selected interval (month, quarter, or year) up to the last day within the current selection.

For example, with MTD (Month-to-Date):

• If you select the 26th of the month, the calculation will include data from the 1st to the 26th.

• If you don’t specify a particular day, it will extend to the latest available date in your calendar.

• In January, for instance, it would cover the full month from the 1st to the 31st.

The same logic applies to QTD (Quarter-to-Date) and YTD (Year-to-Date) calculations.

One important thing to keep in mind: if you don’t provide any context—meaning you don’t filter on the Date dimension—it will always take the maximum date available in your calendar. This approach ensures proper context. On the other hand, functions like PREVIOUSMONTH do the opposite by selecting the earliest possible month, which I personally dislike. To maintain alignment, I avoid such functions.

The good news is that all the functions I’ll list rely on the MAX date instead of the MIN date.

DATESINPERIOD

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals.

Used mainly in CALCULATE’s filter expressions, DATESINPERIOD allows you to select a specific date range based on an interval of days, months, quarters, or years. A common use case is calculating totals for the last 3 months or last 12 months dynamically.

For example, calculating the sum for the last 3 months:

CALCULATE(
	[sum],
	DATESINPERIOD('calendar'[Date], MAX('calendar'[Date]), -3, MONTH)
)

Here, the second parameter (MAX('calendar'[Date])) defines the starting point, typically the latest available date in the current selection. This approach mimics functions like TOTALMTD or YTD, ensuring alignment with the reporting context.

If the max date is 31.12.2024, the calculation will cover 1.10.2024 to 31.12.2024 (full three months). However, if the max date is within a month (e.g., 15.12.2024), it will cover 16.09.2024 to 15.12.2024, as DATESINPERIOD operates on a rolling interval rather than full calendar months unless explicitly controlled.

SAMEPRIODLASTYEAR

SAMEPERIODLASTYEAR(<dates>)
Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.

Used mainly in CALCULATE’s filter expressions, SAMEPERIODLASTYEAR is a game-changer when you need to calculate an existing measure for the same period in the previous year—making it perfect for year-over-year (YoY) comparisons.

For example, if you want to calculate MTD Growth YoY, the first step is to compute the MTD value for last year:

CALCULATE(
	[MTD],
	SAMEPERIODLASTYEAR('calendar'[Date])
)

SAMEPERIODLASTYEAR mirrors the evaluation context of the original measure and simply shifts the date range one year back, ensuring consistency in the comparison.

PARALLELPERIOD

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)
Returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.

Used mainly in CALCULATE’s filter expressions, PARALLELPERIOD works similarly to SAMEPERIODLASTYEAR, shifting the original context by a specified interval. However, with PARALLELPERIOD, you can choose how far and in which unit (months, quarters, or years) you want to shift the period. The syntax is similar to DATESINPERIOD, allowing selection from predefined intervals.

For example, to calculate MTD (Month-to-Date) for the previous month (MoM comparison), you would write:

CALCULATE(
    [MTD],
    PARALLELPERIOD('calendar'[Date], -1, MONTH)
)

Important note:

According to the documentation, PARALLELPERIOD always returns the full range of the shifted period. This means that when shifting by months, it will always return the entire previous month, regardless of the current date selection.

Recommendation: If you need to compare an exact period within a month (e.g., from the 10th to the 21st), use DATEADD instead, as it allows for precise date shifting while maintaining the original selection window.

DATEADD

DATEADD(<dates>,<number_of_intervals>,<interval>)
Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

Personally, I don’t use it often, but since I mentioned it in PARALLELPERIOD, I decided to include it here.

Used mainly in CALCULATE’s filter expressions, DATEADD works similarly to PARALLELPERIOD, with one key difference: it respects incomplete interval selections. This means that if you select only part of a month, quarter, or year, DATEADD will shift that exact selection rather than returning the entire previous period.

Other core functions

Here are some important leftovers—functions that didn’t quite fit into a specific group, or I didn’t want to create a separate category for just one. Still, they’re incredibly useful and definitely worth learning.

SWITCH

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
Evaluates an expression against a list of values and returns one of multiple possible result expressions. This function can be used to avoid having multiple nested IF statements.

Forget nested IFs, SWITCH allows  you evaluate multiple conditions more efficiently and return different results based on those conditions.

The real power of SWITCH lies in the ability to evaluate TRUE(), allowing you to write multiple conditions in a clean, structured way. SWITCH will process each condition sequentially and stop at the first one that evaluates as TRUE.

This is especially useful for color formatting, flagging values, or dynamically changing calculations based on conditions.

Here's and example of conditional formatting:

SWITCH(TRUE(),  
    [sales] >= 500, "#0F0",  
    [sales] >= 250, "#00F",  
    "#F00"  
)

In this case:

• Sales of 500 or more get green (#0F0).

• Sales of 250 or more (but less than 500) get blue (#00F).

• Anything lower than 250 defaults to red (#F00).

ISBLANK

ISBLANK(<value>)
Checks whether a value is blank, and returns TRUE or FALSE.

A great way to check whether a value is blank and act accordingly. ISBLANK is commonly used when you need to return static values (though I generally don’t recommend doing this—but sometimes, you have to).

One typical scenario is when you’re applying flags based on conditions. If you return static text values without proper filtering, they might populate across all rows, significantly hurting performance. ISBLANK helps prevent this by ensuring that static values are only returned for valid rows.

Of course, this is just one of the many use cases where ISBLANK can be useful.

IF

IF(<logical_test>, <value_if_true>[, <value_if_false>])
Checks a condition, and returns one value when it's TRUE, otherwise it returns a second value.

IF in DAX works pretty much the same way as in Excel, which is why I wasn’t sure whether to even include it—but here we are.

Use IF only when you need to compare one value with a single TRUE/FALSE result. If you find yourself nesting multiple IFs, it’s better to switch to SWITCH instead (you see what I did there?), as it keeps your code cleaner and more efficient.

USERPRINCIPALNAME

USERPRINCIPALNAME()
Returns the user principal name.

The sole purpose of this function is to return the current user viewing the report. It’s mainly used for implementing Row-Level Security (RLS) when you need to dynamically filter data based on the logged-in user.

A common RLS setup involves having a table with email addresses, which determines which entities each user can access. You can then create a relationship to your fact table and define an RLS rule like this:

[email] = USERPRINCIPALNAME()

With this setup, you can manage access on the Power BI Service side by adding a distribution group containing all users and publishing the report via app. This way, access control can be handled with a simple file on SharePoint (or any other automated process), making it easy to update permissions dynamically.

BLANK

BLANK()
Returns a blank.

A straightforward function, often used to control what is displayed when an alternative result is needed. While some functions handle this by default, for those that don’t, this is a great way to return BLANK() instead of overloading your visual with unnecessary calculations for rows that are out of context.

DIVIDE

DIVIDE(<numerator>, <denominator> [,<alternateresult>])
Performs division and returns alternate result or BLANK() on division by 0.

Yes, DIVIDE simply divides two numbers, just like using "/". However, I strongly recommend using DIVIDE instead, because it handles division by zero gracefully.

If the calculation results in division by zero, DIVIDE returns BLANK() instead of an error like NaN. Alternatively, you can specify a custom result for such cases using the optional third parameter.

MAXX/MINX

MAXX/MINX(<table>,<expression>,[<variant>])  
Returns the highest/lowest value that results from evaluating an expression for each row of a table.

I know it's an iterator, but I didn’t include them in the core list because they’re not essential for everyday "DAXing". These functions are mostly useful for edge cases or when you need to iterate over a table to find the highest or lowest value.

In some scenarios, you might use FILTER to search for a representative portion of a table that includes the correct context’s value before applying MAXX/MINX, though this isn’t something you’d typically do in every calculation.

A common use case is when you need to pre-filter a table based on your current context—for example, using SELECTEDVALUE—and then find the lowest or highest value within that filtered portion.

Another example is using VALUES to determine the highest or lowest value for a specific aggregation.

Summary

So, here it is—all the core functions you should learn. Naturally, I also use other functions that aren’t listed here, but this serves as a solid foundation for anyone starting with DAX.

It’s also a great way to check if you know enough (though that assumes I know enough—which is probably not true! 😆). Keep learning, see you nexttime.