Filter second table based on selected value in first table . No relationship exists between two tabl

[Jason6]

Thank you in advance for your inputs

I have two tables in my powerbi model , first which is the raw table and the second table which has items listed

"Item"column from Table 1 is the slicer value which user chooses 

I want the second table to be displayed only for those values which correspond to selected value

Problem in below model is we cannot have a relationship as second table has categories concatenated ( it could be 10-15 values in actual model)

Is there any way for this result to be achieved 

For example if user chooses "Category 3" as slicer, i want the second table to be filtered and show "Item3" & "Item2" as both of these have "Category3" in them

I tried to create a calculated column based on selected value measure but it does not work and the calculated column is always blank

[v-eachen-msft · Accepted solution]

Hi @Jason6 ,

You could edit your second table in query editor. Here is my sample data.

1. Split "Column1" by delimiter. 

2. Unpivot your category columns and remove extra columns. Now you could create relationships between two tables.

Then you could use SELECTEDVALUE() function to get a result as a flag in filter pane.

Here is my test file four your reference.

[Jason6]

Thank you everyone for your inputs . Greatly appreciate your help

@v-eachen-msft I was able to achieve the result with your inputs, Thank you 🙂

[amitchandak]

@Jason6 ,  Option 1. Split the column in table 2.

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Keep them diconncted and use in fllter clasue : https://docs.microsoft.com/en-us/dax/containsstring-function-dax

[Jason6]

@amitchandak I tried this and in my actual data i got 27 columns for one record, the value might keep increasing with future files , however even after i do that i would still need to filter based on selected value of table 1 filter

[Pragati11]

Hi @Jason6 ,

Is there any commom column in both of your tables with same format of values or is it just what you have shown in screenshot?

Thanks,

Pragati

[Jason6]

@Pragati11  There is no common value, the behaviour is same as per screenshot

The common values are concatenated 

[Greg_Deckler]

@Jason6 - You should be able to create a selector to do this for you. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Basically, you can grab the value in the slicer using SELECTEDVALUE or MAX/MIN, etc. You can then use that to determine if the row in your second table matches that value and if so return 1, otherwise 0. You can filter the visual on 1.

[Jason6]

@Greg_Deckler  In the file you shared, the Complex selector looks at a number and the measure is set accordingly based on single value 

However in my example i have category labels and i have to use Search or find functionality to search in the second table

How can i encorporate the search feature and then set a measure