Forum Discussion
Filter based on single value existing across multiple columns.
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
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.
3 Replies
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.
- Ashish_Mathur
Super User
Hi,
Remove the second column and then right click on the first column's heading and select "Unpivot other columns".
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.
