Forum Discussion

SivaMani's avatar
SivaMani
Resident Rockstar
7 years ago

Page level filter - Filter a table with 100+ values

Hello folks,

 

I have a situation that I have to filter 100+ value on a table.

  1. I had a look at the Power Query(List.Contains). It won't help in my case
  2. The column that I'm trying to filter has 250+ unique values

Please do help me with this. Thanks in advance

Need Help
Tips and Tricks

6 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi SivaMani,

    It would help if you show a sample of your data. Try this:

    1. Get the 100+ values you want to use for filtering in a one-column table. Let's assume we name it

    AuxTable[Values]

    2. Create a new calculated table by filtering the original table using that auxiliary table:

    NewTable =
FILTER ( Table1, Table1[ColumnToFilter] IN DISTINCT ( AuxTable[Values] ) )

       

    • SivaMani's avatar
      SivaMani
      Resident Rockstar
      7 years ago

      AlB ,

      I don't want to create a new table since I'm using a direct query mode. And it equivalent to creating a view in my backend.

       

      I'm using M query to set a flag for the same and Filtering the flag field in the report.

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, SivaMani 

        In power query, you could try this way:

        Get the 100+ values you want to use for filtering in a one-column table.

        Then click Convert to List as below:

        Now, use Power Query(List.Contains) to add a custom column( flag)

        List.Contains(#"Category",[Category])

        Then use this column to filter the data.

         

        Best Regards,

        Lin

         

         

  • judspud's avatar
    judspud
    Solution Supplier
    7 years ago

    Hi SivaMani 

     

    If i understand correctly you are wanting to filter a table to only displays values greate than 100.

     

    You can drag that field into a visual level filter and use the advanced filtering to say gretaer than 100

     

    Hope this helps.

     

    Thanks,

    George