filter a table base on selected value of slicer on another table

[Moony]

Hi All~ i'm new to Power BI / Power Query (but i did tried to search for ans before asking) so please forgive me if it's a stupid question.

i have 2 tables

Table A with Name (unique) and other info

Name | other columns....

AAA | ... ... ...

BBB | ... ...

CCC | ... ... ...

Table B with column Names (can be multiple names separated by ";"

Names | other columns...

ALL1| ... ... ...

BBB | ... ... ...

AAA;BBB | .... ... ...

AAA;CCC | ... ... ...

ALL2| ... ... ...

i'm trying to create a slicer for user to select the "Name", so the list of records from table B will be filtered accordingly to only show those contain the selected "Name" under "Names"

My initial thought is

create a slicer base on table A "Name" (so unique and single name in the list),

use SELECTEDVALUE to get the choice,

create a calculated column with ContainsString for filtering ( <- this is where i failed, i now know i can't use SELECTEDVALUE on calculated column, or rather the calculated column were calcualted at the beginning and won't be refreshed... )
until i found a solution here
https://community.fabric.microsoft.com/t5/Desktop/Filtering-table-visual-based-on-selected-value-of-...

the remaining problem is i also need to include a few special case (i.e. ALL1, ALL2) which doesn't contain the Name selected.
i don't understand why the solution works so i can't modify the dax myself.....

appreciate if some one can explian in more detail on how that works and show me how i can add extract condition to include those "ALL1" and "ALL2".

Thanks~

[Anonymous · Accepted solution]

Hi,

@lbendlin , Thanks for your concern about the problem, and i want to offer some more information for user to refer to.

hello @Moony ,based on your desrciption.

you need to do some transform for table b in power query.

1. add a conditional column

Output

2.Select the created custom column, and select fill down

Output

3.Then colse and apply

4.Then create a calculated column in table b

Column = IF(CONTAINSSTRING([Names],"ALL"),CONCATENATEX(FILTER('Table B',[Custom]=EARLIER('Table B'[Custom])),[Names],";"),[Names])

5.Create a measure

Measure = var a=COUNTROWS(FILTER('Table A',CONTAINSSTRING(MAX('Table B'[Column]),[Name])))
return IF(a>0,1,0)

6.Then put the measure to the visual filter.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You posted this in the Power Query section but your question seems to be more related to the user interface and DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...