Forum Discussion
Slicer filtering
Hi Community
I am a recent user of PowerBI and so far, it works great for me. I however have a question that I really struggle to answer, despite browsing through the forum. Here it is:
In my datasource, for each row, I have a column which has one or more values. These values are among a defined set of c. 25 values. Hence this column is something like:
| 1 | Elephant, Lion | |
| 2 | Lion | |
| 3 | Elephant, Giraffe | |
| 4 | Lion, Giraffe | |
| ... | ||
| 10,000 | Boa, Panda |
Because I have 25 values (animals if you want) and I have 10,000 rows, I end up with thousands of combinations. I would like to be able to search by filtering a "Field" for one of the 25 values ("lion" for instance) and have my connected powerbi charts updated for these. Filtering by using the "search" function + ctrl+clic is just not going to be efficient over the long run.
In essence, I would like to filter a slicer (with my 25 values) that would drive the filtering of this column of my master database. The following link Power bi slicer contains with examples - Enjoy SharePoint is the closest response for my issue I found but I don't manage to implement.
Any help would be much appreciated!
Thanks!
Laurent from Paris, France
Hi Laurent88
You can refer to the following sample.
Sample data
Samle slicer data
Create a measure and put the measure to the table visual filter
Measure = VAR a = COUNTROWS ( FILTER ( VALUES ( Slicer[Slicer] ), CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Column2] ), [Slicer] ) ) ) RETURN IF ( a > 0, 1, 0 )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
For simple models, it would be possible to have the data in the format shown but for a more database-like structure, the AnimalList column would be split and then the subsequent columns unpivotted. It would turn out like this:
1 Elephant 1 Lion 2 Lion 3 Elephant 3 GiraffeYou can filter this with an Animal Dimension table related 1:m to this table.
If required, you can build animal lists with a measure using CONCATENATEX
Hello, Thanks for the reply!
I confirm my database has 60 columns per row. Would you mind giving me more detail on how to split (keeping the subsequent columns) and then filtering the split columns (if i understand correctly).
Thanks,
Laurent
You can split the column into rows in one move using this.
Hi Laurent88
You can refer to the following sample.
Sample data
Samle slicer data
Create a measure and put the measure to the table visual filter
Measure = VAR a = COUNTROWS ( FILTER ( VALUES ( Slicer[Slicer] ), CONTAINSSTRING ( SELECTEDVALUE ( 'Table'[Column2] ), [Slicer] ) ) ) RETURN IF ( a > 0, 1, 0 )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you all for the help!
