Forum Discussion
Filtering one table from another table's Selected Value
Hey Everyone!
So what I'm trying to do right now is filter one table based on the selectedValue of another table.
The tables are much more expansive than this in terms of content and columns for but simplicities sake:
Table 1:
Column 1:
1
2
3
4
Table 2:
Column1: Column2:
AB1 A
LM1 B
T12 C
S13 D
M24 E
NS4 F
I want a situation where i can click on the value on Table 1 and search Table 2/Column 1 for said selected values:
so selecting 1 would return Column2's A, B, C, D as they all contain 1
and selecting 2 would return Column2's C, E as they all contain 2
I set up a measure in table 1 called CurrentSelection:
CurrentSelection = SELECTEDVALUE(Table1[ID], 0)
and wanted to have a Column set up called SelectedFlag to filter on for table2 that looked like
SelectedFlag = search(Table2[ID], Table1[CurrentSelection], 1, 0)
to then filter on but it seems the Table1[CurrentSelection] always returns 0 in this case. My understanding is this is because Calculated Columns take a single snapshot and won't adjust from a constantly changing measure.
Any help would be appreciated (even if its a confirmation of what I want to do is not possible). This is greatly simplified as it is applying to hierarchies so I alternatives with hard coded in values are a definite no.
Here's what I came up with. PBIX file is attached below.
Used Power Query to parse out the #'s from your table 2, since that is what will be searched for based on the user selection from table 1. Too much to list out here and much easier to see step by step in PQ. But the final table:
You were on the right track with the selected value, so stuck with that:
Col 1 Selected Value = SELECTEDVALUE(Table1[Column1],0)
I used that value and searched in our newly created "Just Numbers" column from above:
Contains = SEARCH( [Col 1 Selected Value], MAX(Table2[Just Numbers]),,BLANK() )
5 Replies
Since this is really dealing with hierarchies, have you thought about using the built-in hierarchy functions? This link might help.
With that being said, i like this challenge and will give it a go
Here's what I came up with. PBIX file is attached below.
Used Power Query to parse out the #'s from your table 2, since that is what will be searched for based on the user selection from table 1. Too much to list out here and much easier to see step by step in PQ. But the final table:
You were on the right track with the selected value, so stuck with that:
Col 1 Selected Value = SELECTEDVALUE(Table1[Column1],0)
I used that value and searched in our newly created "Just Numbers" column from above:
Contains = SEARCH( [Col 1 Selected Value], MAX(Table2[Just Numbers]),,BLANK() )Hi Anonymous,
By my tests, the solution of Nick_M should be your desired output.
If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Cherry
I Have a similar problem but can't see how the solutions suggester here would work for me. I have created a .pbix to show the problem.
I have two tables - the first is incoming help desk cases with column for CaseID, Hardware and Language:
Case ID Hardware Language 12345 Servers English 12346 Storage French 12347 Servers
Italian 12348 Tape Drives German 12349 Servers English The other table is a table of agents with a list of their language and hardware skills. The Skills entry is a list of skills separated by the pipe symbol. There is no order to the skills field, and no defined length. Agents can have 2 skills or 200 skills and they can be a mix of hardware and languages in any order. Here is my example agents table:
Agents:
Tom English|Servers|Storage Jane Networks|Storage|French Harry Servers|French|Tape Drives|English Janet Tape Drives|German|French|English|Storage John Storage|Italian What I want to be able to do is in Power BI have a visual of these two tables, and when the user clicks on one of the cases, e.g. 12345, the agents visual shows only the agents with the skills in their skills list. So in the case of 12345, an English Servers call, it would show only Tom and Harry.
I have got some of the way with the use of SELECTEDVALUE, but I can't see how to restrict the display of agents dependent on the selected value.
Help appreciated!
