Page level filter - Filter a table with 100+ values

[SivaMani]

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

[AlB]

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]

@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]

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

[SivaMani]

@v-lili6-msft ,

Thanks for the response.

I implemented this approach. I'm looking for a similar option in the Page level filter. The problem here is, I have multiple pages with a different list of filters. So I concern about the performance if I follow this approach for all other pages.

[v-lili6-msft]

hi, @SivaMani 

If you want a similar option in the Page level filter.

The best way is that

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

and  create a relationship with basic table and drag the value field from the 100+ values table into Page level filter and set is not blank.

Best Regards,

Lin

[judspud]

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