Blog

Mixing table functions: ALL() & FILTER()

You are currently viewing Mixing table functions: ALL() & FILTER()

Mixing table functions: ALL() & FILTER()

The usefulness of DAX functions lies in using DAX functions together in a manner where they add value to each other. This blog presents a use case of using two DAX table functions together, i.e., ALL() and FILTER(). Before we get into the details of using them together, let’s quickly understand the functionality of each of these individually.

ALL():

ALL() is one of the most widely used table functions in DAX. This function gives same result as DISTINCT() and VALUES() with a column name argument however it is different because it allows you to select more than 1 column and it returns a unique combination of both columns. What makes ALL() stand out in other table functions is its ability to ignore any incoming filter content. The function behaves differently when for a column and a table as an input argument. If a column is used as an argument in ALL() function, then all the unique column values are returned. However, if a table is used as an argument in ALL(), then all rows of the table including the duplicate rows are returned. Like many other table functions, the output from ALL() can be used as iterators for other calculations. A brief overview of the ALL() function is presented in the image below.all-function

For a step by step guide, click here: Using ALL() to Ignore Filters – Power BI Training Australia

FILTER():

FILTER() as the name employs, returns filtered data. The data can be filtered based on one or more conditions. FILTER() is commonly used to create virtual tables which act as iterators for other DAX calculations.filter

For a step by step guide, click here: Using FILTER() as a Table function – Power BI Training Australia

 ALL() & FILTER():

Now that you have learnt the basics of ALL() and FILTER() functions in DAX. Let’s see how these two can be used together in context of a business value.

To use ALL() and FILTER() together:

  1. Click on the Modeling tab in the Ribbon.
  2. Click on New Table.

The DAX expression in the next step basically creates a table based on certain conditions.

  1. Type the following expression in the formula bar and press Enter.filter-expression

In this Expression:

  • A virtual table ignoring any incoming filters from Category, Product and List Price is calculated.
  • FILTER() filters this virtual table and only the products from the category of “Accessories” that have the list price greater than 30 are filtered.
  1. Click on the Data tab in the left Ribbon.
  2. Click on Mixing table functions – FILTER AND ALL (CT) in the Fields pan.
  3. Click on the List Price Column.

Column tools open.

  1. Click on “$” in the formatting section.

Notice that the resulting table contains 3 columns only because ALL() finds the combination of column values and FILTER() finds the subset of rows that belong to the category of Accessories and have list price greater than 30.

Conclusion:

ALL() and FILTER() are two commonly used DAX functions. Each of these two is suited for a particular functionality. However, these two can be used together in a manner where the results of one function act as an iterator for the other. In this blog, a step by step guide to using ALL() and FILTER() together has been presented. 

Why not sign up for Power BI training in Australia. We provide our services in the following regions:
• Power BI Training in Sydney
• Power BI Training in Melbourne
• Power BI Training in Brisbane

Tags: ,

Leave a Reply