How to apply a filter to multiple columns

[Anonymous]

I pretty new to Power BI and i have been trying to create a filter to show me which countries the names have been to but what happens is it only applies to the column "Country 1"

Name	Country 1	Country 2	Country 3
Jake	UK	PH	SG
Patrick	HK	SG	US
John	US	HK	UK

So what happens is if i filter UK it will give me 

Name	Country 1	Country 2	Country 3
Jake	UK	PH	SG

but what i need instead is 

Jake	UK	PH	SG
John	US	HK	UK

since John has been to UK also. The country column is currently 7 columns but it can be a lot more so applying filters to each column wouldnt be ok. Would appreciate any suggestion or reference material i can review for this.

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can create a new measure and apply it to visual filter pane:

filter1 = 
VAR a =
    IF (
        SELECTEDVALUE ( Table1[Country 1] )
            IN VALUES ( Country[Country Name] )
                || SELECTEDVALUE ( Table1[Country 2] )
                    IN VALUES ( Country[Country Name] )
                        || SELECTEDVALUE ( Table1[Country 3] ) IN VALUES ( Country[Country Name] ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( Country[Country Name] ), a, 0 )

Best Regards,
Community Support Team _ Eason

[kmelloCFO]

Hi there, 

I have a similar situation and have attempted this solution but the filter does not produce the results in the original table to then filter by. The answer is always 0 no matter which value is selected.

Thanks

[ericcartman]

Did you ever figure out this problem? I am having the exact same issue right now

[parry2k]

@Anonymous the best is to unpivot your data:

transform data -> select name column -> right click  -> Unpivot other columns

you will get two new column, attribute and value, rename these as you see fit

Close and apply

Create a matrix visual, put name column on rows, attribute on columns, and value on values, also use value as a slice, now if you select UK in the slicer, you will see both the rows in the matrix visual.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

Hi Parry 2k! thank you so much for your feedback! it got a bit complicated with the solution that you gave since im using another visual other than the list. i need to study it more i guess. what i did was i concatinated everything into one column and added a text search visual so it will show me all rows with that data. thank you again!!