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 case I would like to mark ID 2 and 3

 

Thanks in advance

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 

     

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    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 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Really?  No answers ? : )

  • 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.