How to Deal with 5 ALL related Filter Functions in DAX — DAX in Power BI — Chapter 16

We are going to explore ALL, ALLSELECTED, ALLEXCEPT, ALLCROSSFILTERED, ALLNOBLANKROW filter functions in DAX using practical examples.

In any data analysis project, the filter functions of DAX are playing very important roles. Using these functions we can fulfil some customer requirements which are related to the data visualization perspective.

In this blog, you are going to explore how you can use 5 ALL related DAX filter functions ALL, ALLSELECTED, ALLEXCEPT, ALLCROSSFILTERED, ALLNOBLANKROW.

Get Data

This time we will use available sample data which is already available in Power BI Desktop. Let me show you how you can do that.

  1. Open Power BI Desktop.
  2. On the page canvas, you will find “Try a sample dataset”. Click in this.
  3. From the pop-up box, click on “Load sample data” and select the “financials” sheet for the analysis.
  4. Click on Load and save data.
Image by Author

Use of ALL function

Now time to check how ALL function helps us to fulfil reporting requirements.

Syntax
ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

We can apply this function on a table or on columns and it will return table or columns with filters removed.

Let’s watch the practical example.

Use of ALLSELECTED function

At a glance, you will find ALLSELECTED function behaves like ALL functions. But don’t get confused, there is some key difference between these two functions.

Syntax
ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

By syntax, it is the same with ALL, but when we will use ALLSELECTED function, it will remove context filters from columns and rows in the current query while you will apply any external or explicit filters/slicers.

I am sure you want to watch this difference with some practical examples.

Use of ALLEXCEPT function

By name of this function, you can understand, this function removes all context filters in the table except filters that have been used to the particular columns.

Syntax
ALLEXCEPT(<table>,<column>[,<column>[,…]])

Now you will experience the use of this function with examples.

Use of ALLCROSSFILTERED function

This function helps to clear all filters which are applied to a table.

Syntax
ALLCROSSFILTERED(<table>)

I found this function very interesting, let’s watch it together.

Use of ALLNOBLANKROW function

As the name suggested, it will help you, return all rows from the parent table of a relationship and does not count truly blank rows of a table.

Syntax
ALLNOBLANKROW( {<table> | <column>[, <column>[, <column>[,…]]]} )

Are you confused? Don’t worry, here is one example.

Download

Please find the code in the below location

DAX — Chapter 16

Conclusion

The blog teaches us the 5 “ALL” related filter functions of DAX with practical examples.

If you have any questions related to this project, please feel free to post your comments.

Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: