Distinct Count [NOT IN]

[RK9009]

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	B
8	D
8	E
9	A

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

[amitchandak]

@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]

@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]

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

[sqlguru448]

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

[RK9009]

@sqlguru448 ,  no luck buddy

[Greg_Deckler]

@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)))

[RK9009]

@Greg_Deckler , Thank you for the response, I tried this measure but no luck