Power BI DAX: ALL

The ALL function in DAX is a simple yet extremely versatile function that let’s us override any filters or slicers that may be affecting what data is coming into a calculation or visual.

We’re going to learn how it works with a simple example using a CALCULATE function. (I recommend you follow that link to learn the basics of the CALCULATE function too!)

SUBSCRIBE BOX

We’re going to use a very simple dataset to demonstrate how this works, and if you’d like a copy of the Power BI file I use in the below video and written instructions, you can download it here.

Video:

Other posts mentioned in the above video (can also be found down in the written instructions below):

Written Instructions:

We’re starting off with a simple dataset with just two small columns, and a simple table visual that will allow us to see what’s happening in our data as we write DAX.

This table has both our Item and Value columns in it, and by default, tables Sum numerical columns like our Value column as well as put a Total Sum row down at the bottom.

This table (and any chart visual we make) has something called Row Context, which is when the data is split up into rows, and anything we add to the table (or any DAX that interacts with the table) gets filtered into that row context.

First, let’s suppose that we want a column in this table that shows all our values added together (that total of 60) in every row of our table. We may want to see that kind of total in our table (which we’re only really using for demo purposes here. Think about how you may want to use that overall total in a chart… maybe to figure out a percentage?

However, while we can easily find the total of our Values with a SUM function, we run into a problem when we try to use a regular SUM calculation in a visual like a chart or a table like this.

Let’s give it a try so you can see what I mean.

We’ll make a SUM measure that sums up our Values.

Click New Measure up in the ribbon, and use the SUM function:

VALUE SUM = 
SUM('Table'[Value])

If we add this new measure to our table (or any chart), we get our sum getting divided up into categories just like our Value column is:

It’s being split into sums per category because this table (or any chart with categories) has what’s know as Row Context. Row context takes value added to a chart and splits it up into row values because we have those categories in our chart too.

So what we need here is to figure out our SUM, but also tell Power BI to ignore row context. We do NOT want that category filtering going on.

We’re going to do this using the ALL function, but we need to use it within a CALCULATE function.

A CALCULATE function has 2 parts. The 1st part is what we want calculated and the 2nd part is where we can filter what data that calculation is performed on.

Let’s click New Measure up in our ribbon and try this out.

TOTAL SUM = 
CALCULATE(SUM('Table'[Value]), ALL('Table'))

In our measure, the 1st part of our CALCULATE function is our calculation, the SUM of our Value field. The 2nd part of our function though, uses the ALL function and identifies our data table. The 2nd part of a CALCULATE function usually filters the data the calculation is performed on, but in our case we do NOT want a filter, not even the filtering that happens in a table or chart with our categories.

We want ALL the data. We want NO filters.

So, we use the ALL function to tell Power BI to ignore any filters that may be on the data, including Row Context filtering.

Now we can add this new measure to our table and get our overall sum on each row of our table.

Now you can figure out the percentage of each row easily… you have a numerator and a denominator for each category.

Go check out the DIVIDE post to learn how to divide numbers with DAX.

That’s it! That’s the ALL function in DAX. A great way to override any filters on your data!

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.

0 Comments

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>