Forum Discussion
Filter second table based on selected value in first table . No relationship exists between two tabl
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
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.
8 Replies
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.
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
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.
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 🙂
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
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
