Filter based on single value existing across multiple columns.

[samcox88]

Hi There,

I'm hoping to get your assistance with the following..

What I want to achieve with my dashboard is to allow my users to select a single value and that will filter the full paged based on their selection.  The difficulty I’m having is that the data I would like to filter on, is present across multiple columns.  Using the data below as an example, if my user was interested in 'DEF' as a component, then the filter would then return all rows where rowNum = 1, 2 and 3.

RowNum	Components	Component_1	Component_2	Component_3	Assigned_To	Created_On
1	[ABC, DEF, GHI]	ABC	DEF	GHI	User1	1/01/2021
2	[DEF, GHI, JKL]	DEF	GHI	JLK	User2	1/02/2021
3	[ABC, DEF, JKL]	ABC	JKL	DEF	User2	1/03/2021
4	[XYZ, LMN]	XYZ	LMN		User3	1/04/2021
5	[ABC]	ABC			User1	5/02/2021
6	[NOP, KLM, HIJ]	NOP	KLM	HIJ	User4	6/04/2021
7	[]				User4	18/01/2021

What i have done so far -

• Using the Components field as the filter in a slicer, but then they would need to select all rows where they can see DEF to apply this filter.  This could potentially lead to some user error.
• Creating a separate table based on all unique values across all 3 columns.  But as the ID exists across multiple rows i couldn't get the relationship to work.
• Groupings, but given this would have to be manually updated every time a new component was introduced i don't know if this was the best approach?

Any assistance you can offer in this would be greatly appreciated.

Cheers,

Sam 

[Anonymous · Accepted solution]

Hi @samcox88 ，

I create a separate table based on all unique values across one column. Like this:

Use the column as a slicer .

Then create a measure to judge whether the specified content is in data table[Components], if it is ,returns 1;if it is not, return 0, and then filter the value of measure in the visual filter and set it to display only 1. The result returned is what you want .

Measure =

var _measure =

var _a=SELECTEDVALUE('Table (2)'[Component])

var _b=SELECTEDVALUE('Table'[Components])

return CONTAINSSTRING(_b,_a)

return if (_measure,1,0)

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Remove the second column and then right click on the first column's heading and select "Unpivot other columns".

[Anonymous]

@samcox88 

The table you have is not fit for modeling in PBI. You must unpivot columns Component_1, *_2, *_3 first or create a bridge table from this one to a table that holds all individual components. Then it'll be very easy to do what you want.

Here's how to do Modeling in PBI or here.