Forum Discussion
Distinct Measure producing incorrect results
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:
- 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")))
- 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
- Measure 1 = 68794
- Measure 2 = 62038
- 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
HI Anonymous,
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
1 Reply
HI Anonymous,
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