Blog

Our Power BI DAX training covers the table functions in detail because they are widely used for calculating information out of raw data. DISTINCT(), VALUES() and ALL() can given similar results under certain conditions. DISTINCT() and VALUES() were covered in blogs published last week and this week’s blog is dedicated to getting our users familiar with ALL() function in DAX.

ALL() gives same result as DISTINCT() and VALUES() with a column name argument but allows you to select more than 1 column and it returns a unique combination of both columns.

Getting familiar with ALL()

ALL() has a special feature which makes it different from all other functions. ALL() ignores the filter context. The result of ALL() depends on the input argument:

• When the table is passed as an argument, all the rows of a table returned (including duplicate rows) are returned ignoring any filter context. These values can be used by an X-function or iterators.
• When the column is passed as an argument, all the unique values are returned.

ALL() using single column:

To see how ALL() is used to create tables:

1. Click on the Modeling tab in the Ribbon.
2. Click on New Table.
3. Type the following expression in the formula bar and press Enter.

In this Expression:

• The unique product categories from the Product table are found because the argument to ALL() is s table column

4. Click on the Data tab in the left Ribbon.
5. Click on Testing Table Functions using ALL (CT) in the Fields pan.

ALL() using multiple columns:

To understand the behavior of ALL() using multiple columns:

6. Click on the Modeling tab in the Ribbon.
7. Click on New Table.
8. Type the following expression in the formula bar and press Enter.

In this Expression:

• Two columns from the Product table are used as arguments in the ALL() function.

9. Click on the Data tab in the left Ribbon.
10. Click on Testing Table Functions using ALL – two col (CT) in the Fields pan.

Notice that distinct combination of category and sub category are returned. Let’s see what happens when two columns from different data sets are used as arguments in ALL().

11. Click on the Modeling tab in the Ribbon.
12. Click on New Table.
13. Type the following expression in the formula bar.

You cant select two coloumns from different tables. Now let’s see what happens when a table and column (of a different table) are used as arguments in ALL().

14. Click on the Modeling tab in the Ribbon.
15. Click on New Table.
16. Type the following expression in the formula bar.

The warning shows that this type of syntax combination is not allowed in ALL().

This can be done using SUMMARIZE(). Which will be explained in the upcoming blog.

Conclusion:

ALL() is a table function which is commonly used to ignore the incoming filter context. The output depends on the arguments used as input of the function. The output for a single column argument in ALL() is different from a multiple column argument in ALL(). This blog provides a brief summary of ALLL() with working example for each of the above mentioned cases.  

Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide the following Power BI trainings:

• Power BI Essentials
• Power BI Advanced
• Power BI DAX