Slicer to check if value exists in any column, then filter to those rows

[JJJ109]

Hi everyone,

Reasonably new to Power BI, so greatly appreciate any assistance with my current challenge.

I have a table of data, showing the sports an individual plays. When an individual plays more than one sport, they are spread across multiple columns, as shown in the 'SampleValues' table below.

I am hoping to create a single slicer which allows a report user to select one sport, filtering the SampleValues table to anyone playing that sport. For example, if tennis is selected in the slicer, it would return rows 1, 6, and 7, and 9.

I have tried using a concatenated column (with COMBINEVALUES) and a new table for slicer values ('SlicerValues', below); however, as the values in SlicerValues (e.g. Tennis) don't match exactly with concatenated values (e.g. Tennis;; or Basketball;Tennis;), it does not properly filter.

I have also tried setting up relationships between the SlicerValues table and Sport1,Sport2,Sport3 columns in SampleValues, but Power BI seems to restrict to only one relationship per table pair.

'SlicerValues' Table:

Sport

Baseball

Basketball

Cricket

Football

Soccer

Tennis

'SampleValues' Table:

Name	Age	Sport 1	Sport 2	Sport 3	Sport Summary
1	a	Tennis			Tennis;;
2	b	Soccer	Football		Soccer;Football;
3	c				;;
4	d	Basketball	Cricket		Basketball;Cricket;
5	e	Football			Football;;
6	f	Football	Cricket	Tennis	Football;Cricket;Tennis
7	g	Tennis			Tennis;;
8	h				;;
9	i	Basketball	Tennis		Basketball;Tennis;

Many thanks in advance for any suggestions or advice you many have.

[ProjectKM]

If you don't need to see Sports 1, Sports 2, and Sports 3,
Then unpivot the columns and connect them to Sport

Here's a sample

'Slicer Value' table

'Sample Values' table

and table visual

I hope it helps

[amitchandak · Accepted solution]

@JJJ109 , One way is to have active and inactive join, both of them with slicer assume sport 1 is active and sport 2 is inactive

Create a measure to count

M1=  count(SampleValues[Name]) + calculate( count(SampleValues[Name]), userelationship(SampleValues[Sport 2], SlicerValues[Sport]) )

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

or refer

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2