Strange result for ALLEXCEPT

[ccyangdi]

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?

[ImkeF · Accepted solution]

This is due to the different granularities within your one-table model and the order in which you use its fields in the visuals (see: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ ) 

I recommend start build a proper star-schema for your data in order to use ALLSELECTED : https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/ 
This should explain why one-table-models are a road to disaster in Power BI.