Slicer filters measure despite ALL condition

[hs248]

Hello,

consider the table

Letter	Number
A	1
A	1
A	2
A	2
B	3
B	3
B	3

and the measure 

Test = CALCULATE(COUNTROWS(Table),ALL(Table[Number]))

On my report page I have two slicers, one filtering Letter and one filtering Number, and a card visual containing the measure "Test". I have set the interactions for both slicers to filter the card but not each other.

If I set the Letter slicer on "A" and the Number slicer on "1" or "2", the value of the measure is 4, as excpected:

But if the Letter slicer is "A" and the Number slicer is "3", the value of the measure is Leer, which is 0, instead of 4:

Despite the ALL(Table[Number]) statement in the CALCULATE function, the Number slicer seems to somehow combine with the Letter slicer and interfere with the measure. I don't understand this - what's going on and how can I prevent this? I would like the measure to behave as if the Number slicer wasn't there.

In reality, the measure is more complicated. I can't just set the Number slicer to not filter the card visual, as I need the Number filter later on in the measure.

Thanks for help and explanations!

[hs248]

Thanks for the clarification, @kpost , you are right.

But I can also give more detail on the problem: In the end, I would like to implement a Kolmogorov-Smirnov Test in order to check whether two non-overlapping subsets of the table stem from the same distribution. One subset shall be chosen by the Letter slicer and the other by the Number slicer. In order to achieve this, the DAX Code of the measure contains the lines

VAR m=CALCULATE(COUNTROWS(Table),ALL(Table[Number]))

VAR n=CALCULATE(COUNTROWS(Table),ALL(Table[Letter]))

RETURN [Result of some calculation involving m and n]

I haven't mentioned this background at first, because it adds nothing to the actual problem and might only be confusing.

[kpost · Accepted solution]

I created a solution:

To do this I expanded the data model with two new dimension tables:

NUMBER_TABLE = DISTINCT('Table'[Number])

LETTER_TABLE = DISTINCT('Table'[Letter])

And here is the DAX:

test = CALCULATE(COUNTROWS('Table'), ALL(NUMBER_TABLE[Number]))

Make sure that the slicers on the page are the columns from your dimension tables LETTER_TABLE and NUMBER_TABLE.

You can also now do your calculations, such as the following, which yields the correct answer of one.  (4 minus 3, when A and 3 are selected)

Test =

var letter = CALCULATE(COUNTROWS('Table'), ALL(NUMBER_TABLE[Number]))

var num = CALCULATE(COUNTROWS('Table'), ALL(LETTER_TABLE[Letter]))

RETURN

    letter - num

See attached .pbix file.

///Mediocre Power BI advice, but it's free///

 

[hs248]

Thanks, @kpost , that's exactly what I needed. But I still do not understand the logic of Power BI far enough to know WHY my previous solution did not work, whereas yours works... Any hints?

[kpost]

There are usually a few ways to do something, and in this case there's most likely a way to do it with only DAX. 

To be honest I tried a similar approach as you for a while and got stumped, so I went to something that I was sure would work.   Perhaps somebody else will come along and provide a more elegant solution, but at least you have something that works, for now, and can continue with your project.

///Mediocre Power BI advice, but it's free///

[amit_maniyar]

Hi @hs248 ,
I think it would be better if you can provide the problem statement in detail- like what are you trying to achieve before we come to selection of DAX functions to achieve that?

Also, I can observe that A letter has a pair with Number 1 and 2 but not with 3 as per the data. Both of your slicers selections make impact on the cue card value. Therefore, when you select letter A in slicer 1 filters out 3.

Hope that might help you understand the issue here.

thanks,

AM

[kpost]

The desired behavior asked for  by @hs248  is that without modifying visual interactions, the card will produce the number 4 when "A" and "3" are both selected.  The desired behavior is to count the number of rows in 'Table' where Letter matches the Letter(s) selected in the Letter slicer, regardless of what is selected in the Number slicer.