Filtering one table from another table's Selected Value

[mlchan]

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. 

[schulbert]

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!

[v-piga-msft]

Hi @mlchan,

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

[Anonymous · Accepted solution]

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()
)

PBIX file: https://1drv.ms/f/s!AoQIGRpzoxRH0y64D4Ue5nYGNGbj

[mlchan]

Thank you so much! This is the exact concept I was looking to do (works perfectly with the complex model).

[Anonymous]

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