Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Check if list contains only one specific value

Hi

 

I have a following list

 

ID - Label 

1 - A

1 - B

1 - B

2 - A

2 - A

3 - A

4 - B

4 - C

5 - A

5 - B

5 - B

5 - C

 

What I want to know is what ID's contain ONLY A's ?

 

In this case I would like to mark ID 2 and 3

 

Thanks in advance

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@Anonymous

 

Hi, try with this measure:

 

OnlyLabelSelected =
VAR LabelSelected =
    VALUES ( Table1[Label ] )
VAR V_ID =
    VALUES ( Table1[ID] )
RETURN
    IF (
        HASONEVALUE ( Table1[ID] ),
        IF (
            COUNTROWS ( FILTER ( Table1, Table1[Label ] = LabelSelected ) )
                - COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[ID] = V_ID ) )
                = 0,
            1,
            0
        )
    )

 

Finally Use the measure in Visual Level Filter 

 

OnlyLabels.png




Lima - Peru

View solution in original post

4 REPLIES 4
Vvelarde
Community Champion
Community Champion

@Anonymous

 

Hi, try with this measure:

 

OnlyLabelSelected =
VAR LabelSelected =
    VALUES ( Table1[Label ] )
VAR V_ID =
    VALUES ( Table1[ID] )
RETURN
    IF (
        HASONEVALUE ( Table1[ID] ),
        IF (
            COUNTROWS ( FILTER ( Table1, Table1[Label ] = LabelSelected ) )
                - COUNTROWS ( FILTER ( ALL ( Table1 ), Table1[ID] = V_ID ) )
                = 0,
            1,
            0
        )
    )

 

Finally Use the measure in Visual Level Filter 

 

OnlyLabels.png




Lima - Peru
Back2Basics
Helper IV
Helper IV

You can do it visually quite easily... if that helps

 

try using the treemap visualisation. The first one select 'Label' as the group and 'ID' as the values and the second one select 'ID' as the group and 'Label' as the values.

 

Then select Label A (as I have done in the picture) and it will quickly show you the amount of 'A's in each ID - and hence the ID's that only have As. I haven't found a simple way of doing what you are after in a neater way - but I will keep thinking.

Capture.JPG

Or you can do something like this in the matrix preview visualisation. If you can figure out a way to only show where the column entry is 100% then you're off I think. But this is a quick way of doing it and seeing the results.

 

Untitled.png

Anonymous
Not applicable

Really?  No answers ? : )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.