Filter a table with an and/or statement based on a filter value

[Anonymous]

Hi I have two tables, table A has a list of names, table B has two columns (Columns Name1 and Name2) where those names can exist.

I want to filter table B where either Name1 and/or Name2  = the filtered name in Table A.

Table A

Peter

John

Sally

Michelle

Stuart

Table B

Name1	Name2
John	Clare
Chris	Stuart
Sally	Michelle
David	Peter
Robert	Chris
Clare	Jennifer

Filtering using Table A Table B should show those in Blue

[vicky_]

You can go into the relationships tab and create a relationship between Table A's Name column, and Table B's Name1 column. 
When you create your table, put TableA[name] and TableB[Name2]. Right click on TableA[name] in the "Columns" section and make sure that "Show items with no data" is unchecked. That should get you the desired output without the need for a filter.

[Anonymous]

Thank you Vicky, this almost works accept it still shows rows where both names are blank (i.e Last two ros on Table B Above).

I also want to filter to so by selecting Chris, I can see the rows where Chris exists in Name1 or Name2 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below to judge if the name1 or name2 exist in table A

Flag = 
VAR _selname1 =
    SELECTEDVALUE ( 'B'[Name1] )
VAR _selname2 =
    SELECTEDVALUE ( 'B'[Name2] )
RETURN
    IF (
        _selname1
            IN VALUES ( 'A'[Name] )
                || _selname2 IN VALUES ( 'A'[Name] ),
        1,
        0
    )

2. Create a table visual using  table B data and apply a visual-level filter with the condition(Flag is 1)

If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Thankyou @Anonymous that is similar to what I ended up doing.  I created a table with Distinct and Union to get the same result.  Thank you,  have amrked as a solution.