Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
and a PBI page:
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?
Would be very grateful if someone could demistify all this.
Solved! Go to Solution.
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?
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.
I suggest to watch this video on evaluation context https://www.youtube.com/watch?v=1yWLhxYoq88
Proud to be a Super User!
Well, it's an exellent video, but it does not answer my questions. In fact it introduces yet another filter - the initial filter.
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.
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?
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!
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.
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.
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") )
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"?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
72 | |
49 |
User | Count |
---|---|
142 | |
139 | |
110 | |
69 | |
55 |