Forum Discussion
Clearing filter context from key column
Hello Team,
I am having a problem with understanding what filter function should I use in my example:
I have some simple model with fact table and 2 dimensions with attributes and 2 measures where:
sales_measure=SUM('fact'[sales])
total=CALCULATE([sales_measure],ALL('fact'[color id]))
So I am using ALL('fact'[color id]) to clear filter contex from this column which is also key column in relationship with color dimension.
Now I would like to use a slicers from color dimension but when I am using it is not working as I want and my total measure is affected by this slicer - in my understanding it shouldn't because 'dim'[color id] affecting 'fact'[color id] (relationship) and on this column I have ALL function applied to clear all filters on that column.
Then, when I am using color id column from fact table for slicer, it is working fine:
My conclusion is that ALL(column) function is not able to clear filters that are coming by relations. However I noticed that ALL(table) function is able to clear all context even from relations.
But I can't just simply use this ALL function on a table because in my real project I have much more slicers that are coming from different tables and I don't want to clear the context from them by apllying ALL on my whole fact. I just want to clear the context from my one column (that is key column to dimension), but I want this column to be cleared also when I am using fields from this dimension as a slicers.
I know that If I want to slice data by fields from dimensions I could use then ALL(column from dimension) function, but the hardest thing here is that I have to use only fields from fact to clear the context from this one dimension.
Any thoughts or useful functions here? 🙂
Hi Anonymous ,
For the difference between ALL(column) and ALL(table), you can check this blog:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI.
Then in your scenario, try this:
total = CALCULATE ( [sales_measure], TREATAS ( ALL ( 'fact'[color id] ), 'Dim'[Dim color id] ), ALL ( 'fact'[color id] ) )Here's my test example:
Reference: TREATAS function - DAX | Microsoft Docs
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , Dimension will filter Fact(1-M) , Fact will filter dimension only if join is bi-directional
You can stop interaction between two slicers if needed
https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions
- Icey
Community Support
Hi Anonymous ,
For the difference between ALL(column) and ALL(table), you can check this blog:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI.
Then in your scenario, try this:
total = CALCULATE ( [sales_measure], TREATAS ( ALL ( 'fact'[color id] ), 'Dim'[Dim color id] ), ALL ( 'fact'[color id] ) )Here's my test example:
Reference: TREATAS function - DAX | Microsoft Docs
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
