DAX Power BI: advanced filtering using ALL, ALLEXCEPT, ALLSELECTED, ALLBLANKROW
In DAX, it is often necessary to remove filters imposed on a table before performing an aggregation. Four functions are responsible for this process:
- ALL
- ALLEXCEPT
- ALLSELECTED
- ALLBLANKROW
These functions work directly with filters: for example, removing part or all filters previously imposed on the table.
ALL() function — returns all rows and columns of the source table, removing and clearing previously imposed filters on it.
Let’s try two variants of using this function
ALL( Table)
If filters had previously been applied to this table, the filters would have been cleared.
ALL( Column list )
Then we specify the parameter client identifier. Then the function works according to the second kind of syntax.
Again, if filters were applied to this column, they would be cleared. In this case we see the full e-mail list of all clients.
Now let’s add another column to the parameters of this function
As a result, we see that we have all combinations of the E-mail client and the Manager column displayed — and we have a table of 240 rows.
By adding a third column we get an even longer table
If we specify the columns of our table as a parameter, all unique rows from combinations of those columns are returned to us.
Now let’s create filters on the dashboard for each of our filtered columns and create measures.
First, let’s create a measure on the source table
Then we create a measure with the ALL function on the whole table and get the same result
After which we’ll add another measure
Let’s test our measures using different filters
As we can see different variants of the ALL function fix the tables created by us in table measures and we get a result that changes for the first case, but is preserved in the second and third cases.
Visualization of filter action
Don’t forget to subscribe to
and join our Power BI community
