Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Visual advanced filter multiple values

I have created a table visual to show a list of items that do not have a valid status. I have a list of valid statuses (A, D, N, S, O, and more) and want to show any item with anything other than the...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You need an extra table (suppose it's 'ValidStatus') to list all valid statuses.

     

    Create such a measure:

    Measure =
IF (
    ISERROR (
        FIND (
            SELECTEDVALUE ( DataTable[Status] ),
            CONCATENATEX ( ValidStatus, ValidStatus[ValidStatus], "," )
        )
    ),
    0,
    1
)

    Add this measure to visual level filters and set its value to 1.

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

You need an extra table (suppose it's 'ValidStatus') to list all valid statuses.

 

Create such a measure:

Measure =
IF (
    ISERROR (
        FIND (
            SELECTEDVALUE ( DataTable[Status] ),
            CONCATENATEX ( ValidStatus, ValidStatus[ValidStatus], "," )
        )
    ),
    0,
    1
)

Add this measure to visual level filters and set its value to 1.

 

Best regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Yuliana. I decided to create an additional column with a switch statement for each valid value and setting to 1 if valid or 0 if not. Then I could key off this column for my visual.