topic Re: Distinct Count [NOT IN] in DAX Commands and Tips

Distinct Count [NOT IN]

RK9009 — Tue, 08 Sep 2020 23:51:26 GMT

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.

Re: Distinct Count [NOT IN]

Greg_Deckler — Wed, 09 Sep 2020 00:07:30 GMT

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

Re: Distinct Count [NOT IN]

sqlguru448 — Wed, 09 Sep 2020 00:16:04 GMT

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

Re: Distinct Count [NOT IN]

amitchandak — Wed, 09 Sep 2020 01:13:50 GMT

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

Re: Distinct Count [NOT IN]

RK9009 — Wed, 09 Sep 2020 16:46:38 GMT

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

Re: Distinct Count [NOT IN]

RK9009 — Wed, 09 Sep 2020 16:47:29 GMT

sqlguru448 , no luck buddy

Re: Distinct Count [NOT IN]

RK9009 — Wed, 09 Sep 2020 16:49:04 GMT

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

Re: Distinct Count [NOT IN]

Greg_Deckler — Wed, 09 Sep 2020 17:26:59 GMT

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.