ALL

How to Use the Power BI DAX function ALL

What is the ALL Function?

The ALL function in DAX is used to remove all filters from a specified column or table. It returns all the rows in the table without any filter. This can be very useful in situations where you need to compare data across different subsets or groups. For example, you may want to compare sales figures for different regions, but you don’t want the region filter to affect the results. This is where the ALL function comes in handy.

How to Use the ALL Function

The ALL function can be used in a variety of ways in Power BI. Let’s take a look at some examples:

Using ALL with a Single Column

Suppose you have a table named “Sales” with columns for “Region,” “Product,” and “Sales Amount.” You want to create a measure that calculates the total sales amount for each product across all regions. To do this, you can use the ALL function to remove the filter on the “Region” column. Here’s how you would write the DAX formula:


Total Sales = CALCULATE(SUM(Sales[Sales Amount]), ALL(Sales[Region]))


This formula tells Power BI to calculate the sum of “Sales Amount” for all rows in the “Sales” table, but to remove any filter on the “Region” column. This means that the result will show the total sales amount for each product across all regions.

Using ALL with Multiple Columns

You can also use the ALL function with multiple columns in Power BI. Suppose you want to create a measure that calculates the average sales amount for each product across all regions and all years. To do this, you can use the ALL function with both the “Region” and “Year” columns. Here’s how you would write the DAX formula:


Average Sales = CALCULATE(AVERAGE(Sales[Sales Amount]), ALL(Sales[Region], Sales[Year]))


This formula tells Power BI to calculate the average of “Sales Amount” for all rows in the “Sales” table, but to remove any filters on both the “Region” and “Year” columns. This means that the result will show the average sales amount for each product across all regions and years.

Using ALL with Measures

You can also use the ALL function with measures in Power BI. Suppose you have a measure named “Total Sales” that calculates the sum of sales amount for all rows in the “Sales” table. You want to create a measure that calculates the percentage of total sales for each product across all regions. To do this, you can use the ALL function with the “Product” column and the “Total Sales” measure. Here’s how you would write the DAX formula:


% of Total Sales = DIVIDE(SUM(Sales[Sales Amount]), [Total Sales], ALL(Sales[Product]))


This formula tells Power BI to divide the sum of “Sales Amount” by the “Total Sales” measure, but to remove any filter on the “Product” column. This means that the result will show the percentage of total sales for each product across all regions.

The ALL function in DAX is a powerful tool that can help you analyze data in Power BI more effectively. It allows you to remove filters from a specified column or table and compare data across different subsets or groups. Whether you’re working with a single column or multiple columns, the ALL function can help you gain deeper insights into your business operations. By mastering the ALL function, you’ll be able to create more accurate and meaningful reports in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

15-17 July 2024
16-18 September 2024
11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)