Calculate Allexcept to neglect only few slicers

[pranit828]

Hi All,

I have the below table and slicer on columns Tabl[Cl], Tabl[Qu] and Tabl[Op]

Cl	Qu	Op	Sn
A	A1	O1	1
A	A2	O2	2
A	A3	O3	3
B	B2	O2	4
B	B3	O3	5
C	C3	O3	6
C	C4	O4	7

When I don't select Tabl[Op] slicer I get the count as 3 from the below measure.

Tot = CALCULATE(COUNT(Test_tbl[Sn]),ALLEXCEPT(Test_tbl,Test_tbl[Cl],Test_tbl[Qu]))
OR
Tot = CALCULATE(COUNT(Test_tbl[Sn]),ALLSELECTED(Test_tbl[Cl],Test_tbl[Qu]))

What I need is to count the total rows independent of the selection in [Op] and dependent on the selection in [Cl] and [Qu].

So, It should always return 3 if I change only the selection in [Op] slicer in the above screenshot.

The sample file is attached at the bottom.

[AlbertoFerrari · Accepted solution]

Hi,

I did not check the details, but it looks to me that this is nothing but the auto-exist behavior described here: Understanding DAX Auto-Exist - SQLBI. 
Give it a look, try a star schema and LMK if it works. 
Best and... enjoy DAX! 🙂

[AlbertoFerrari · Accepted solution]

Hi,

I did not check the details, but it looks to me that this is nothing but the auto-exist behavior described here: Understanding DAX Auto-Exist - SQLBI. 
Give it a look, try a star schema and LMK if it works. 
Best and... enjoy DAX! 🙂

[selimovd]

Hey @pranit828 ,

that behavior is for me super strange.

I tried a basic version:

Tot_New = CALCULATE( COUNTROWS( Test_tbl ), ALL( Test_tbl[Op] ) )

But for some reason the ALL( Test_tbl[OP]) is totally ignored. There is also no sort by column what would explain that.

The following query is sent when I use the slicer of Test_tbl[Op]:

I have no idea why this happened.

Maybe that's a case for @marcorusso and @AlbertoFerrari ?

Best regards

Denis

Blog: WhatTheFact.bi

[marcorusso · Accepted solution]

It's the side effect of combining SUMMARIZECOLUMNS with auto-exists. 
Long story short: use a star-schema.

[AlbertoFerrari]

🙂 This is what happens when they summon us both 

[selimovd]

@AlbertoFerrari  @marcorusso 

Sorry about that, next time I will just mark one of you and the time afterwards the other 😉

Didn't know about Auto-Exists, very interesting topic! Every day in DAX I learn something new 😅