Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 fo...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

schulbert's avatar
schulbert
New Member
2 years ago

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 IDHardwareLanguage
12345ServersEnglish
12346StorageFrench
12347

Servers

Italian
12348Tape DrivesGerman
12349ServersEnglish

 

 

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:

 

TomEnglish|Servers|Storage
JaneNetworks|Storage|French
HarryServers|French|Tape Drives|English
JanetTape Drives|German|French|English|Storage
JohnStorage|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!