Power BI – All and AllExcept in DAX

By: David Rohlfs

Introduction

There are a lot of times when working with DAX where your equations and calculations just don’t work. This happens especially when you start working with dates and times. One of my favorite solutions to fixing DAX equations that are using date/time intelligence is to use the All or All Except functions.

In this blog we will go over what the All and All Except functions are, when you should use them, some problems that you may have while using them, and a short walkthrough demonstrating the use of these functions.

When to use it

The most often times where I find a need for the All function are when I am working with dates and times. This is because the equations tend to filter for a single date, and this isn’t always what you need to get readable data. Here is an example where I need to use an All filter to fix my situation.

In this example, all we want to do is get the sales for a single day (January 18, 2024). But our goal is to get the value for that day to be on all of the dates inside of our table. Let’s start with our first measure (Measure 1). We are calculating the sum for our sales and filtering for the specific day that we want. Our result is good because it shows the value that we want, but I want this value to be on every row in our table.

Now let’s build out Measure 2 that will use the All function to solve this problem. You can see that I used an All function on the Date Dimension table so that for every date inside of the date dimension, we will get our sales for January 18, 2024.

Now that I have shown you what the All function does, let’s talk about it. The all function is looking at a table and will either read the entire table or display your result to the entire table. In the example above, I am using the All function to take the value determined in the calculate function and posting it to the entire date dimension.

This is a pretty simple idea but let’s talk about when you should use this. Recently, I have been doing a lot of calculations considering dates and determining YTD, PYTD, MTD, etc. and some of the calculations and functions won’t give me an accurate amount for the entire period but create a running total.

When this happens, I generally choose to use an All function so that I get the total amount and don’t need to use any other functions.

The whole goal with using an All function this way is to allow your data to be more easily found. If I left the table like it is above where every row shows the total amount, it can easily be interpreted incorrectly by the end user. Because of this, I try not to use DAX equations that contain the All function inside of tables or matrixes, but instead where there is a single value output like a card, or another subsequent equation.

Now that we have gone through the All function, we can start to talk about the All Except function in DAX. All Except is similar to the All function, but it has the ability to group data together. Unlike the All function, the All Except function is not generally used as just a table expression but is more commonly used in a calculate function to group together category amounts. Here is an example:
I have a sales table with 12 rows of data and three different products. Instead of using a matrix, I am choosing to use a measure that can group my data together for me. My goal is to get the total sales amount for each sale and to get the total sales amount for each product. I am not concerned with the total sales per product being a running total. To get my category amount I am just going to use the DAX equation shown in the screenshot below.

You can see that all of my Apples have the same Measure 5 amount, along with all of my Pears being the same, and Oranges being the same. Using the All Except function allowed me to essentially do the same thing that the All function does, except for on a finer scale.

Common Problems

There have been some very complex formulas that I have worked on with the Dynamic Date Selection blog where using the All functions in the wrong spot would not give me the result I was expecting. To fix this I would have to layer the All functions or use the All functions on different tables. This has seemed to be an isolated situation, but it is worth noting that with very complex formulas, I have had some problems with the All function in DAX measures.

Walkthrough

Let’s go through an example of using an All function. We are going to use this function in the Calculate function because it already has a Filter portion available. But you can use the All function within a lot of different functions that ask for a table, or for a filter.

I want to get the sales for only 2022 and my goal is to show this amount on every line of data for future comparisons. To do this I am going to use a measure. Do this by clicking on the table you want the measure to be placed, then select New Measure in the Top Table Tools Ribbon.

Now I am going to write out my Measure Equation. I am naming this measure Walkthrough Measure. I will use a Calculate Function, a Sum function, and for my filters I will use a filter to get only rows that are in the 2022 year, then use an All function to place this value on every row of my Walkthrough Example table.

Now, let’s see what this measure looks like in a table with my Date Dimension Date Field.

I am getting my entire years sales value for 2022 on every row. This is what I wanted because I can use this to create other measures later on.

Conclusion

While the use of the All and All Except functions are fairly simple, they can be very important when you are working with date and time calculations. If you enjoyed this blog, I recommend reading through some of EPM Strategy’s other blogs linked below. I always try to link related blogs so that whether you are working through a specific problem or just trying to learn more about a subject, there are quick options for you to choose from.

 

Links Related to This Blog:

Date Time Format

Has One Value

Group By

Why Create a Date Table in Power BI

Comments