Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Solved! Go to 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! 🙂
It's the side effect of combining SUMMARIZECOLUMNS with auto-exists.
Long story short: use a star-schema.
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! 🙂
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 ?
It's the side effect of combining SUMMARIZECOLUMNS with auto-exists.
Long story short: use a star-schema.
🙂 This is what happens when they summon us both
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 😅
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |