top of page
Search

# DAX- All about All( )- Easy way

This blog is dedicated to the basics of “ALL” function only. Let's explore everything about "ALL" function in power Bi. ALL is one of the calculate modifiers and acts as a filter in the calculate function.

The first thing to keep in mind is that “All” is a table function, it returns a table and not a scalar function. using ALL function along with calculate will give scalar output.

ALL([Tablename/columnName],[column1],…)

“ALL” returns all the rows in a table or all the values in a column, ignoring any filter that might have been applied.

ALL([Table name]) is a table function that can be used by using the table tools and new table tab.

This will return the same table as mentioned in syntax.

first, we will start with the very basic use of the ALL function which is to create a new table from an existing table.

1. Load data in Power Bi

2. Do the basic cleaning steps. I am using a clean and small dataset.

3. Go to data view and select the new table (remember All is a table function)under the table tool

4. Write the name for the table and ALL and table name example sample data

5.All sample data = ALL(‘sample data’) and enter or commit.

6. All sample data with selected column = All(‘ sample data’[Country],’sample data’[Product],’sample data’[Segment])

7. This will return the exact copy of the original data loaded in Power Bi

8. You might wonder why we should make copies of data for no reason

9. Plus we can easily make duplicates of data in power query with few clicks

10. So the reason is to use this table function with scalar functions like sum, sumx, Average, Averagex, and so on

11. When we use this function with other functions, “All” function creates a temporary table.

We create temporary tables or write subquery, or CTE to get answers for get filtered table.

When we use the “All” function in combination with other functions, it will give more flexibility to write dax and manipulate data and eventually create rich analytical power

13. Now we will use “All” in measures

14. Keep in mind that all measures created in Power BI work irrespective of the context filter

15. Measure will return different values according to the context of visual and explicit filters used like slicer.

Total Sale = SUM(‘sample data’[Net Sales])

The total sales measure in a card shows showing overall total sales because it has no other context.

But the same total sale measure we used in the table with some additional columns country and product, the measure changes the value according to the context of both columns. If we remove one of the columns from the table, values will change according to visual context.

after removing one of the columns let's say product, the card visual will remain the same because the external filter is not applied and the card has no visual context.

Total Sales in the context of country and product

Total Sale in the Context of Country

So in a nutshell, measures work in the context of filters.

Now let's see after applying an external filter country in Slicer.

After applying an external filter

1. card visual showing total CPU sales in all countries together

2. Table showing CPU sale breakdown irrespective of countries in table visual

It will reflect in table visual and card visual because the total sale measure respects applied filters (it is a measure and works in the context of filters).

Measures work in the context of visual and filter context is an advantage and disadvantage at the same time.

let's see if we want to see % of Canada sales contribution to total sales.

then we need a measure to show total sales which will not get affected by any visual and external filters.

In that case, we use the “ALL” table function in measure.

let's create one measure that doesn't get affected by any filter

All total Sale = CALCULATE(SUM(‘sample data’[Net Sales]),ALL())

Before external filter

• Now the “All total sale” measure is created and we can see it has already passed the first check

• In the table visual “Total sale” measure is broken down with visual context, but “All total sale” shows the same value, it is not affected by visual context.

• Now lets check with other filters

• Looking at both screenshots, we can say it is also ignoring external filters as well.

• Finally, we got a measure that is not affected by visual context and external filters.

• Now it is easy to perform the % contribution of each country for total sales

• let's write one Dax formula to get the % contribution.

• % sales by country = DIVIDE([Total Sale],[All total Sale])

% contribution to total sales by each country

Let's see “All” with tablename as a parameter and All with column name

All total sale tablename = CALCULATE([Total Sale],ALL(‘sample data’))

After product filter

After the product and country filter

“ALL” with no parameter

All total Sale = CALCULATE(SUM(‘sample data’[Net Sales]),ALL())

“ALL” with tablename as a parameter

All total sale tablename = CALCULATE([Total Sale],ALL(‘sample data’))

“ALL” with no parameter and “ALL” with tablename as a parameter are the same. If no parameter is provided it will treat tablename as a parameter

“ALL” with Column name parameter

All Total sale with columnName =CALCULATE([Total Sale],ALL(‘sample data’[Product]))

“All” Total Sale with columnName” measure shows the total of All product sales in each country. Giving total sale of all products in each country.

2. Table visuals are affected by the visual context of a country column and not by product visual and product filter context.

3. “All Total sale with columnName” is calculated based on the country not by considering any product that's why after selecting only the product filter the total remains the same as before applying the product filter

Before applying filter

“All Total sales with columnName” are not affected by the product filter.

After selecting CPU, only the product column is filtered to CPU but the “All total sale columnName” will remain the same for Canada which is 129515.

After applying the product filter

“All Total sales with columnName” are affected by the country filter.

The filtered tables show the cumulative total Sales of all products in each country. Ignoring the context of all the products from the product column.

After country filter

After the country and product filter

“All Total sale with columnName” is not affected by the visual context of the column specified in the argument (product)but by another column ( country)

“All Total sales with columnName” is calculated based on the country column but not on the product column.

“All Total sale with columnName” for Hard Disk

“All Total sales with columnName” for CPU and “All Total sales with columnName” for Hard Disk are the same.

But these values change as we choose the country filter

Card Visual and table visual react to country filter

In conclusion “ALL” is a table function and can be used in measures to filter rows from a table or all the values from the column ignoring any filters that might have been applied. It is similar to “REMOVEFILTERS”.

Removefilters total sale = CALCULATE([Total Sale],REMOVEFILTERS(‘sample data’))

With or without external filters the total sales for both will remain the same

“ALL” and “REMOVEFILTERS” return same output. The only difference is “REMOVEFILTERS” is calculate modifier filter, whereas ALL is a table function along with a calculate modifier.

I hope this blog is helpful. In the next blog, we will see " ALLSELECTED " and "ALLEXCEPT"

Thank you……………..

57 views1 comment
bottom of page