Ignore Filters For Dimensions (Categorical Variables)

[Ivan_P]

Hi, 

I am failry new to PBI (coming from Tableau) and I am struggling to understand whether PBI can generate a categorical variable ignoring filters. I used measures like SUM/MIN/AVG in combination with CALCULATE/ALL to ignore filters for continuous varibles but I can't make it work with categorical variables.

Let's say I have a categorical column with with 4 distinct values and a visual filter that filters out one of the four values. Can I generate a table that still lists all 4 despite the filter?

I tried this:

CALCULATE(VALUES('Table'[Cat_Var1]), ALL('Table'[Cat_Var1]))

But it still lists only 3 out of 4 distinct values.

Thanks!

[Anonymous]

ALL, ALLEXCEPT, ALLSELECTED should be what you are looking for. In a quick example, I have the following table:

Then write a simple sum measure to get the value of whatever I put on rows:

Amount of each value = sum( 'Table'[Amount])

Then I put a slicer on Value, but no matter what I still want that total of 50 :

Amount of All= CALCULATE( [Amount of each value], ALL( 'Table'[Value]))

Not sure if that is what you were asking, but took a shot.

[Ivan_P]

Not exactly, taking your example - you are ignoring filters for the measure of the count, like at the end shows 50 no matter which category you select. What I am trying to do is to list all 4 values of the dimension (blue, organce, yellow, red) regardless of which value is selected in the filter. Don't know if it is possible.

[v-xuding-msft · Accepted solution]

Hi @Ivan_P ,

If columns are from the same table, or if they are from different tables but there are relationships among them, data will always be filtered when you selected the filter.  So if you want to show all the values whenever the filter selected, you need to create a new irrelative table. However, this no sense for the filter.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ivan_P]

Ok. No means No I guess.