Distinct Measure producing incorrect results

[DanielSchmitz]

Hi all,

This is my situation:
I'm importing data from Active Directory. By using UserAccountStatus I determine which machines are active or not. Besides that I have a conditional column which aggregates different OS versions into a single OS - all working fine.

I then have 3 measures which I would like to use to further filter / calculate specific devices and here I have a problem.

I use the following measures:

1. All active clients in AD

AD Total = 
CALCULATE(DISTINCTCOUNT('AD Data'[computer.cn]), 
FILTER('AD Data', 'AD Data'[userAccountControl]= VALUE("4096") || 
'AD Data'[userAccountControl] = Value("4128") || 
'AD Data'[userAccountControl] = Value("69632")))

1. All active clients in AD with Win 7 OR Win 10

AD In Scope = 
CALCULATE(DISTINCTCOUNT('AD Data'[computer.cn]), 
FILTER('AD Data','AD Data'[OS] = "Win 7" || 
'AD Data'[OS] = "Win 10"), 
FILTER('AD Data', 
'AD Data'[userAccountControl]= VALUE("4096") || 
'AD Data'[userAccountControl] = Value("4128") || 
'AD Data'[userAccountControl] = Value("69632")))​

• All active clients in AD w/o Win 7 OR Win 10

AD Out Scope = 
CALCULATE(DISTINCTCOUNT('AD Data'[computer.cn]), 
FILTER('AD Data','AD Data'[OS] <> "Win 7" && 
'AD Data'[OS] <> "Win 10"), 
FILTER('AD Data', 
'AD Data'[userAccountControl]= VALUE("4096") || 
'AD Data'[userAccountControl] = Value("4128") || 
'AD Data'[userAccountControl] = Value("69632")))

From my understanding if I do cross check I would think that Measure 2 + Measure 3 = Measure 1

1. Measure 1 = 68794
2. Measure 2 = 62038
3. Measure 3 = 6760

However in my case the total number is less than the sum of 2+3 (68798) as you can see.

Anyone any idea why this is happening? If I load the RAW Data into Excel, remove the duplicates and Pivot I get accurate numbers. This happens with a second table as well where all of a sudden 1 or 2 machines seems to disappear.

Thx for any help in this matter

Daniel

[v-shex-msft · Accepted solution]

HI @DanielSchmitz,

Dax formulas will use 'AND' logic to link all filter conditions, I think you need to double-check on your table to confirm all of the records are matched with 'win7'/'win10' conditions. Or they will exist some of not match records that different than 'measure 1' results.

In addition, the sum of 'distinct count' results may not the same as the 'distinct count' of aggregate results. Some of the items existed in different conditions but they not auto remove from the calculated in the aggregate level.

For example:

distinct count 'formula 1' with conditions 1: ABCD -> 4.
distinct count 'formula 2' with conditions 2: ACE -> 3.
distinct count 'formula 3' with conditions 3(1 or 2) : ABCDE -> 5 ≠ 7(formula 1+formula 2)

Regards,

Xiaoxin Sheng