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 ...
amitchandak's avatar
amitchandak
Super User
5 years ago

RK9009 , Using distinct at the final stage should work better, if the solution works

 

countrows(distinct(except(selectcolumns(filter(Table,Table[Category] ="B"), "ID", Table[ID]),selectcolumns(filter(Table,Table[Category] ="A"), "ID", Table[ID]))))

  • 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.