Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
Need Help
Show and Tell
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    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's avatar
Back2Basics
Icon for Resolver I rankResolver I
9 years ago

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's avatar
    Back2Basics
    Icon for Resolver I rankResolver I
    9 years ago

    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.