Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gvg
Post Prodigy
Post Prodigy

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

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!

View solution in original post

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!

View solution in original post

13 REPLIES 13
danextian
Super User
Super User

 

 

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

@danextian

 

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

Hi @gvg

 

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


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

Proud to be a Datanaut!

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.

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!

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?

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!

I have a question on the shadow filter: You mentioned, "the ALLSELECTED function, it will remove the internal/implicit/query filtering on the calendar year, but still apply the filter coming from the slicer."
However, using the DAX query from a visual shows (in the picture): Using ALLSELECTED in measure4 doesn't override any implicit filter (filter1 and 2).
My Question is do you have an example shows how ALLSELECTED shadow filter restoring external filter and overriding internal filters? 
Note: SQLBI's example (CALCULATETABLE + ADDCOLUMNS) is not a practical example and doesn't make any sense!
2023-05-18 11_25_13-DAX Studio - 3.0.7.png

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

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!

@Phil_Seamark

 

OK, thanks! Can you tell me please, why would my measure ratio2 give wrong result? I have added a few more categories to your example here. If I replace 

 

ratio2 = CALCULATE(sum(Table1[Value]),FILTER(ALL(Table1[Country]),Table1[Country]="Canada"))

 

with 

 

ratio2 = CALCULATE(sum(Table1[Value]),FILTER(ALL(Table1),Table1[Country]="Canada"))

I get correct result. But why would ALL(Table1[Country]) not work? I try to work with as few table columns possible as adding additional columns significantly slows down the queries.

Hi @gvg

 

The top [ratio2] is not giving a wrong result as such.  It's giving exactly what you are asking it to do.

 

First of all, you clear all filters from the 'Table1'[Country] column using the ALL function.  This will clear any internal or external filters you have on this field. 

 

The table visual you are using has two internal filters, the first (1) comes from the first column which is the 'Table1'[Country] field, while the second (2) is using the 'Table1'[Other Cat] field.

 

image.png

 

Your top row returns a value of 45 for [ratio2].  Your calculation is a basic SUM(Value) and starts by having access to every row in the table and wants to return a value of 190.

 

Step 1. There are no external filters so the calculation will return 190

 

Step 2. There are now internal filters that the Country must be 'Canada' and the Category must be 'A'.  At this point the SUM would return 45.

 

Step 3. You now clear any filters from Step 1 and Step 2, but only for the Country field.  The only filter in place now is the internal filter that the Category must be A, so at this point the SUM function would return 55

 

Step 4. You now introduce a new filter in the DAX calulcation that Country = "Canada" which reduces the rows visible to the SUM function back to a single row.

 

For the second row, the [ratio2] meaure returns 20.  This is a completely independant calculation and starts again from scratch.  The only difference here is that Step 2 has two different internal filters that Country = Canada, but now that Category = B.  Step 3 clears the filter on the country field, but this is introduced back in Step 4.

 

When using calculated measures, you need to think about the visuals you are going to be using them with, especially if you want to control the filtering, as the fields used in visuals can have an impact.

 

Perhaps you can try this approach to only clear the internal filters from Country and CAT.  This will be more efficient than using ALL, but if you then start to bring additional fields into your visual, you may need to update your measure.

 

ratio2 = 
    CALCULATE(
        sum(Table1[Value]),
        FILTER(
            ALL(
                Table1[Country],
                Table1[Other Cat]
                ),
                Table1[Country]="Canada")
                )

 

 

 

 

 


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

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

I am not sure I understand from step 3. I thought Filter function is effectively filtering the whole table so that Sum function sees only two rows where Country="Canada"?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors