Clearing filter context from key column

[Anonymous]

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? 🙂

[Anonymous]

That is not what I was asking.

The question is how can I clear the filter context in fact table that is coming from one dimension, by using field from a fact table. ALL(fact) is working here but it is clearing as well the rest of filter context from other dimenstions that I would like to keep.

[Icey · Accepted solution]

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.

[amitchandak]

@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