Forum Discussion
Dynamic filtering of data
I have report where the purpose is to filter the data down to a relevant subset of a large data set. Typically this requires the users to apply filters on 3-6 columns, but the relevant columns to filter on are different for different use-cases.
Is there a way to set up a Power BI so that
1) User selects relevant columns to filter on from a dropdown list
2) Power BI displays sliders for the selected columns
3) User filters data
With 40-50 potential 'filter-columns' the number of potential relevant filter combinations is large (1.000.000++ in theory, 100+ in real life), so applying bookmarks to handle this is not a good option.
I would also like to avoid the Filter-pane because I find that to not be user-friendly...
The way to do it is to :
1) add a row-index column (unique value for all rows) to original table
2) duplicate original table, and unpivot copied table. The new table should now contain 3 columns: row-index, attribute (column name) and value
3) manage relationship between filter-table and original table (cross filter direction = Both)
4) add 2 sliders to report page (columns from filter-table):
slider 1: Field = Attribute (dropdown, single-select = On) --> This is used to select column to filter on
slider 2: Field = Value --> This is used to select alowed values for selected filter
5) to add more filters: redo step 2-4
- Note that each slider-pair in the report equires a new unpivoted duplicate of the original data-table.
- The solution may therefore not be applicable for large data sets
5 Replies
vesan , The other option is to have a table with all column names and column values. but that too means you have to handle this code to filter each column.
Unpivoting all columns in the table will make it large.
Those are other two options
I am able to create the table with column names and column values, and set up a filters based on this, but I do not know how I can use this to filter the original table based on the selections.
Hi vesan ,
As far as I know, currently in Power BI is not supported to achieve this feature.
We can unpivot the source table and build relationship based on the public column to filter the summarized data like this:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The way to do it is to :
1) add a row-index column (unique value for all rows) to original table
2) duplicate original table, and unpivot copied table. The new table should now contain 3 columns: row-index, attribute (column name) and value
3) manage relationship between filter-table and original table (cross filter direction = Both)
4) add 2 sliders to report page (columns from filter-table):
slider 1: Field = Attribute (dropdown, single-select = On) --> This is used to select column to filter on
slider 2: Field = Value --> This is used to select alowed values for selected filter
5) to add more filters: redo step 2-4
- Note that each slider-pair in the report equires a new unpivoted duplicate of the original data-table.
- The solution may therefore not be applicable for large data sets
