Filter a table visual if it contains values from a filter which is based on an unrelated table

[Frank_W]

Hello friends,

I have 2 tables

Tables1

Customer	Product
A	Oranges, Apple, Banana, Melon
B	Apple
C	Banana
D	Peach, Banana
E	Peach, Melon

Tables2

Product_Type

Apple

Peach

Banana

Melon

Orange

I want to create a filter Based on "Table2" , I then want Table1 visual to change based on the filter choices of Table2

ie if the user select "Apple" and "Melon" in the filter then Table2 visual will show Customers A,B and E

hope this explains things

thankyou

Frank

[AlanP514]

Hai @Frank_W 

Testing =

VAR SelectedProduct = SELECTEDVALUE(Tables2[Product_Type])

RETURN

CONCATENATEX(

FILTER(

Tables1,

CONTAINSSTRING(Tables1[Product], SelectedProduct)

),

Tables1[Customer],

", "

)
try this code

[Frank_W]

Hello Alan P514,

thankyou very much for your code above, it works but the only issue I have is that when I place the data in a a table visual all of the data is in one row instead of one row per customer name

I appologies as It was my fault, as I should have said 
output = 

Customer

A

B

E

[AlanP514]

Please Share your PBIX file with me

[Frank_W]

Hello AlanP514

Here is a link to the file

https://drive.google.com/file/d/1Sx_TNaUzhXaA3sf5KsvfGclI0Wsu6cHM/view?usp=sharing 

Frank

[Anonymous · Accepted solution]

HI @Frank_W,

You can try to use the following measure formula to compare between two table field values and return flag, then you can use it on visual level filter to filter records:

formula =
VAR productList =
    CALCULATE (
        CONCATENATEX ( VALUES ( Table1[Product] ), [Product], "," ),
        ALLSELECTED ( Table1 ),
        VALUES ( Table1[Customer] )
    )
VAR result =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table2 ),
            SEARCH ( Table2[Product_Type], productList, 1, -1 ) > 0
        )
    )
RETURN
    IF ( result > 0, "Y", "N" )

Regards,

Xiaoxin Sheng

[vicky_]

You can use this guide on setting up an XOR filter based on your selections: https://apexinsights.net/blog/or-xor-slicing

and then you can use CONTAINSSTRING() as part of your filter conditions.

Honestly, I would recommend that you go back to PowerQuery / your data source and change the data structure so that you don't have to use CONTAINSSTRING(), and can just match the entire cell contents.