cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gvg Member
Member

Mysterious DAX filter terminology

Hi experts,

 

I am really getting lost in all the filter terminology. While reading different articles I come across this list of filters:

 

1. Context filter

2. Explicit filter

3. Implicit filter

 

Not to mention shadow filters described here. Leaving the latter ones for rocket scientists could you please explain on a practical example which filters are which. Say, I have a simple model:

 

Capture.JPG

 

and a PBI page:

 

Capture2.JPG

 

 

With regard to Sales result visual:

 

- If have a date slicer on my PBI page - what kind of filter that is?

- What kind of filter is the Date column in the visual?

- If I filter Division by West in the Divisions table visual, what kind of filter West is in the Sales result visual context?

- If I then place filter in the Filter section of Visualizations tab - what kind of filter this is?

 

pbix file is here. 

 

Would be very grateful if someone could demistify all this.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Mysterious DAX filter terminology

By "Explicit" filters, they mean filters that are set outside the current query.

 

In Power BI, every visual on the report canvas is it's own query.  The query can be filtered either internally (by items on the axis of the visual, or by row/column header items), or externally (a slicer or filter that exists outside the visual).

 

Say you have a slicer on a report page showing countries, and you make a selection on the slicer to be USA.

 

Then you create a column chart visual on the same page using a calendar year on the axis and a SUM of sales as the value.

 

Each column in the chart probably has a different value.   The internal/implicit/query filter is applying a filter on each column for the calendar year (coming from the AXIS).  But there is also some filtering being applied by the external/explicit filter coming from the Slicer being set to USA.

 

If you use the ALLSELECTED function, it will remove the internal/implicit/query filtering on the calendar year, but still apply the filter coming from the slicer.  This will probably mean you will see the same value over and over for each year (which will be a total figure for USA).

 

Does that help?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: Mysterious DAX filter terminology

Correct.

 

Have a look at the PBIX file I've attached to this message.

 

I have three calculated measures, all doing the same SUM, but have different filter handling.  You'll see the Slicer and Menu filter have the same effect.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

12 REPLIES 12
danextian New Contributor
New Contributor

Re: Mysterious DAX filter terminology

 

 

I suggest to watch this video on evaluation context https://www.youtube.com/watch?v=1yWLhxYoq88

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
gvg Member
Member

Re: Mysterious DAX filter terminology

@danextian

 

Well, it's an exellent video, but it does not answer my questions. In fact it introduces yet another filter - the initial filter. 

Super User
Super User

Re: Mysterious DAX filter terminology

Hi @gvg

 

I have 2 chapters in my book on DAX that deal with this particlular question Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gvg Member
Member

Re: Mysterious DAX filter terminology

I have read everything on internet regarding this, also the Definitive Guide by the Italians - but have not found a clear answer. In theory everything looks fine, but when it comes to practical examples, it does not turn to be that simple.

Super User
Super User

Re: Mysterious DAX filter terminology

Have you read this article by Jeffrey Wang?  It uses older terminology, but describes what is happening under the covers.

 

http://mdxdax.blogspot.co.nz/2011/03/logic-behind-magic-of-dax-cross-table.html

 

But in a nutshell, there are only two levels of context.  Filter and Row context. The challenge is to keep track of where items can be added to your (initially filtered) filter context.

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gvg Member
Member

Re: Mysterious DAX filter terminology

No doubt, this is a great article. But why most articles talk about "initial" filters, but not implicit and explicit ones that are referred to all the time in Microsoft documention? For example Microsoft describes ALLSELECTED like this: "Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters". So what are those "explicit" filters?

Super User
Super User

Re: Mysterious DAX filter terminology

By "Explicit" filters, they mean filters that are set outside the current query.

 

In Power BI, every visual on the report canvas is it's own query.  The query can be filtered either internally (by items on the axis of the visual, or by row/column header items), or externally (a slicer or filter that exists outside the visual).

 

Say you have a slicer on a report page showing countries, and you make a selection on the slicer to be USA.

 

Then you create a column chart visual on the same page using a calendar year on the axis and a SUM of sales as the value.

 

Each column in the chart probably has a different value.   The internal/implicit/query filter is applying a filter on each column for the calendar year (coming from the AXIS).  But there is also some filtering being applied by the external/explicit filter coming from the Slicer being set to USA.

 

If you use the ALLSELECTED function, it will remove the internal/implicit/query filtering on the calendar year, but still apply the filter coming from the slicer.  This will probably mean you will see the same value over and over for each year (which will be a total figure for USA).

 

Does that help?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

gvg Member
Member

Re: Mysterious DAX filter terminology

Yep, that makes sense. So filter in the Filter section of the Visualizations tab is also an explicit filter, right?

Super User
Super User

Re: Mysterious DAX filter terminology

Correct.

 

Have a look at the PBIX file I've attached to this message.

 

I have three calculated measures, all doing the same SUM, but have different filter handling.  You'll see the Slicer and Menu filter have the same effect.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!