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:

 

IDCategory
1A
2B
3C
4D
5A
5B
5C
6A
6C
7B
7C
8A
8B
8D
8E
9A

 

  • Distinct count of the IDs is 9 
  • Distinct count of IDs in Cat. A is 5

Now, when we count the distinct IDs in Category B, we need to exclude the IDs which were counted in A (i.e Distinct count of IDs in Catergory B should have a result of 3 and not 4 because the ID 8 is already counted in Cat. A once)

 

Please HELPP. 

 

 

 

7 Replies

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    RK9009 Try:

    Count in B Measure =
  VAR __As = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Category]="A"),"ID",[ID]))
  VAR __Bs = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Category]="A"),"ID",[ID]))
RETURN
  COUNTROWS(DISTINCT(EXCEPT(__Bs,As)))
  • sqlguru448's avatar
    sqlguru448
    Helper III
    5 years ago
    count in B Measure =
    VAR __As = DISTINCT(SELECTCOLUMNS(FILTER('Table',[category]="A"),"ID",[ID]))
    VAR __Bs = DISTINCT(SELECTCOLUMNS(FILTER('Table',[category]="B"),"ID",[ID]))
    RETURN
    COUNTROWS(DISTINCT(EXCEPT(__Bs,__As)))
     
    Thanks