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 valid ones. On the visual advanced filtering, I can only have 2 (status is not A or status is not D).

 

Any help would be appreciated!

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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for replying quickly.

       

      Here's a sample of my data:

      ItmNbr   Status

      123         A

      456         D

      789         X

       

      Both A & D are valid statues. I would like to show in my report:

      ItmNbr   Status

      789          X

       

      There is currently no table with valid status. Would it be beneficial to make one?

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