Check if list contains only one specific value

[Anonymous]

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

[Vvelarde · Accepted solution]

@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 

[Back2Basics]

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.

[Back2Basics]

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.

[Anonymous]

Really?  No answers ? : )