Detect ID's in the table which doesn't have specific Category in Power BI

[sarath5140]

Hi Folks,

Need your help in solving the below issue. I have the below table

Id	ExternalId	Category
1	717	Implement
1	346	Test
2	589	Implement
2	646	Other
2	758	Test
3	891	Other
4	1078	Implement
4	967	Other
4	999	Data

Ideally, my output should have for each ID, there should be atleast one Category values as "Test". Now, i want my output should show only ID's which don't have atleast 1 Catgeory as "Test"

Output :-

Id	externalId	Category
3	891	Other
4	1078	Implement
4	967	Other
4	999	Data

Thanks

[DoubleJ · Accepted solution]

Hi

A calculated column with this formula should do what you need:

HasTestCategory = 
VAR ThisId = 'Table'[Id]

RETURN
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Category] = "Test" && 'Table'[Id] = ThisId))
    >0,
    TRUE(),
    FALSE() )

It might not be very performant if you have a large table. In your visual you can simple filter all the items with HasTestCategory = FALSE.



Hope this helps (if so give me kudo ;-))
JJ