Distinct count of IDs excluding any that match a certain category value

[lightw0rks]

Is it possible to get a distinct count of values in the ID column excluding any ID's that have a category value of 'D'

Using the below table as an example, the expected result would be '3': ID 1 + ID 3 + ID 4 = 3 unique ID's

ID 2 is excluded from the count as it has a category value of D.

ID	Category
1	A
1	B
1	C
2	A
2	B
2	C
2	D
3	A
3	B
4	A
4	B
4	C

I've tried using DISTINCTCOUNT() with a filter like [Category] <> 'D' but this doesn't work as it will still include ID 2 in the count since ID 2 still pairs with category values other than D.

Thanks!

[amitchandak · Accepted solution]

@lightw0rks , Try a measure like

countx(filter(summarize(Table, Table[ID],"_1", countx(filter(Table, Table[Category] ="D"),[Category])+0), [_1] =0),[ID])

[lightw0rks]

Thanks @amitchandak and @parry2k , both solutions work great!

[parry2k · Accepted solution]

@lightw0rks or you can use this measure:

Count = 
VAR __tableWithD = CALCULATETABLE ( VALUES ( 'Test (2)'[ID] ), 'Test (2)'[Category] = "D" )
RETURN
COUNTROWS ( EXCEPT ( VALUES ( 'Test (2)'[ID] ), __tableWithD ) ) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak · Accepted solution]

@lightw0rks , Try a measure like

countx(filter(summarize(Table, Table[ID],"_1", countx(filter(Table, Table[Category] ="D"),[Category])+0), [_1] =0),[ID])