Forum Discussion
How to apply a filter to multiple columns
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.
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
5 Replies
- v-easonf-msft
Community Support
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 _ EasonHi 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
- Did you ever figure out this problem? I am having the exact same issue right now
- parry2k
Super User
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.
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!!
