How do I get the value from selected slicer and then filter on another table?

[Anonymous]

Hi,

I have two tables(Sorry I'm new to Power BI so I had to explain in detail bear with me)

TableA

TableB

EMP_ID

A001

A002

A003

A004

A005

I am trying to create a slicer on "Path" from TableA but when I search there are multiple results and I'll manually have to select everything, what if I have 100+ results?  It's not ideal to select everything manually and morever none of any free/propriatary Power BI sliecer's allow me to select all after search. So Instead I thought I can create another table which only has unique strings in my case Emp ID's where I can select and use the SELECTEDVALUE to fiter TableA

Let's say I've picked "A001" from TableB now I want to filter TableA[Path] if it contains the SELECTEDVALUE from the TableB slicer.

I thought I can create a column in TableA which gives me TRUE/FALSE in each row if a SELECTEDVALUE contains in TableA[Path] of that row and I used the below DAX formula

=Contains(TableA, TableA[Path], SELECTEDVALUE(TableB[EMP_ID])

But there are two problem the Column wont change dynamically from what I read so I have to create a measure but If I use the same DAX formula as a measure it will return TRUE even if a single row contains the selcted value and all no filtering is done.

Please advise

a) if a Coulmn can be dynamically changed based on filter selection?

b) A dax formula which filers TableA[Path] if the selected value from TableB[EMP_ID] contains in TABLEA[Path] row wise.

Thanks in advance.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, for your case, you could try this way as below:

Step1:

Create a measure as below:

To filter = SEARCH(SELECTEDVALUE(TableB[EMP_ID]),SELECTEDVALUE(TableA[Path]),1,0)

Step2:

Then drag this measure into visual level filter and set it is greater or equal to 1.

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[rohitkushwaha]

I have same problem but I want to filter on multiple selection on the slicer, how to achieve this? with single selection the code is working fine but not with multiple selection.

[Anonymous]

@v-lili6-msft 

Thank you that's a perfect solution.

[Anonymous]

@amitchandak From my understanding treatas will let me create a virtual relation but I dont understand How I can create a relation when both columns are completely different. Could you please shed some insights on how I can use it?

@shzaidi Awesome this is a simple and excellent fix. But it doesnt show me the search results which is a drawback in situation if I use names and if two people have same name.

@SivaMani CONTAINSSEARCH is not accepting a column in the first value it only accepts individual value not an entire column.. Ideally i did think about this but as it was not accepting the column I thought it wont be useful, if there a way I can pass the entire column?

[SivaMani]

@Anonymous ,

Please use SEARCH function - https://docs.microsoft.com/en-us/dax/search-function-dax

[Anonymous]

@SivaMani 

I cannot use Search in a measure as it wont let me select a column in the whitin text field.

[SivaMani]

@Anonymous ,

a. No. Calculated Column won't change dynamically

b. CONTAINSSTRING(<within_text>, <find_text>)

1. Use CONTAINSSTRING function and create a measure. CONTAINSSTRING(TableA[Path], SELECTEDVALUE(TableB[EMP_ID])) in TableA.
2. Add this measure in your table visual and filter TRUE in Visual level filter. It will work in your case.

Hope this will help you!

[shzaidi]

You can use Text search visual for path field. Type A001 it will bring all path which have A001. Import Text search from market place and use it. It is free. 

[amitchandak]

@Anonymous , check if treatas can help

https://docs.microsoft.com/en-us/dax/treatas-function