Slicer filtering

[Laurent88]

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

[Laurent88]

Thank you all for the help!

[Anonymous · Accepted solution]

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.

[HotChilli]

https://learn.microsoft.com/en-us/power-query/split-columns-delimiter#split-columns-by-delimiter-int... 

You can split the column into rows in one move using this.

[Laurent88]

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

[HotChilli]

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 Giraffe

You 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