cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sarath5140
Helper I
Helper I

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

Hi Folks,

 

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

 

IdExternalIdCategory
1717Implement
1346Test
2589Implement
2646Other
2758Test
3891Other
41078Implement
4967Other
4999Data

 

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

 

IdexternalIdCategory
3891Other
41078Implement
4967Other
4999Data

 

Thanks

1 ACCEPTED SOLUTION
DoubleJ
Responsive Resident
Responsive Resident

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.

testcategory.png

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

View solution in original post

1 REPLY 1
DoubleJ
Responsive Resident
Responsive Resident

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.

testcategory.png

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.