Forum Discussion

RK9009's avatar
RK9009
Frequent Visitor
5 years ago

Distinct Count [NOT IN]

Hi Community I need help with dax    I have a table with IDs and Category:   ID Category 1 A 2 B 3 C 4 D 5 A 5 B 5 C 6 A 6 C 7 B 7 C 8 A 8 ...
RK9009's avatar
RK9009
Frequent Visitor
5 years ago

amitchandak ,tried this one seem like its working in terms of count but the ID counted in A are still showing up in B. 

Thank you for your response 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

RK9009 - I mocked this up in PBIX, Page 11. I missed a couple ALL statements.

 

Count in B Measure = 
  VAR __As = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table (13)'),[Category]="A"),"ID",[ID]))
  VAR __Bs = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table (13)'),[Category]="B"),"ID",[ID]))
RETURN
  IF(MAX([Category])="B",COUNTROWS(DISTINCT(EXCEPT(__Bs,__As))),BLANK())

 

Fixes the showing up in A problem. PBIX below sig. The answer is actually 2, not 3 because 5 and 8 are both in A according to your test data.