Forum Discussion

ccyangdi's avatar
ccyangdi
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Strange result for ALLEXCEPT

script for the 4 measures are as below:
TotalIdByVersion = CALCULATE(DISTINCTCOUNT(Demo[Id]), ALLEXCEPT(Demo, Demo[Version]))
TotalIdByVersionAndTime = CALCULATE(DISTINCTCOUNT(Demo[Id]), ALLEXCEPT(Demo, Demo[Timestamp], Demo[Version]))
TotalIdByTime = CALCULATE(DISTINCTCOUNT(Demo[Id]), ALLEXCEPT(Demo, Demo[Timestamp]))
TotalId = CALCULATE(DISTINCTCOUNT(Demo[Id]), ALLSELECTED(Demo[Vertical], Demo[SubVertical], Demo[SubSub]))
 
 
We want to get the total distinct [Id] count by [Version], which will be filtered by [Timestamp].
The measure TotalIdByVersion was working well without filtering by [Timestamp], so I add [Timestamp] to it to get the number I want. 
 
For version 1.10, total distinct [Id] without [Timestamp] filtering should be 5131, with [Timestamp] 1/5/2020 - 1/18/2020 should be 4354 (the number we want). We can see in screenshot 1 & 2, without filtering by Version slicer, the TotalIdByVersionAndTime in top table is correct, but in the lower table when extending to next level, the number is showing 211 (Q1: how did this happen and how can I get the correct number?).
 
In screenshot 3, as soon as I filtered 1.10 by Version slicer, the TotalIdByVersionAndTime in top table became wrong, and it behaved totally different compare to TotalIdByVersion (Q2: how did this happen?)

 

So far I thought the TotalId is still working, then I clicked AA in Vertical in top table. Then you can see in screenshot 4 that TotalId also became wrong. I think this is because I kept filter for [Vertical], [SubVertical], and [SubSub] for TotalId, and it is filtered by Vertical AA from the first table.

 

Q3: So how can I get the number I want in lower table? 

 

And where can I upload my Demo PowerBI report for your reference?

 

 

 
 
Need Help

1 Reply