cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
powerbisach Helper I
Helper I

set Multiple values in visual filter

I have a user table where user is mapped to countries

UserCountryStateemail
ABCIN  
XYZ MH 
DDDMU  
DDDPU  

Based on user login email i get the associated country. Then this country user[country] is used in measure to check if it is equal to another country country[country] in visual filter with "Yes" and "No" option. The measure is set to Yes to show only applicable country data. This works fine for one to one mapping, but when a user is mapped to multiple countries i am getting multiple values passed error...Any solution, workaround would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-jayw-msft
Microsoft

Re: set Multiple values in visual filter

Hi @powerbisach ,

 

Modify the measure like:

Measure = if(SELECTEDVALUE(Country[Country]) in VALUES(User[Country]),"yes","no")

Result would be shown as below.

3.PNG

4.PNG5.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

View solution in original post

10 REPLIES 10
Super User IV
Super User IV

Re: set Multiple values in visual filter

Can you share your formula?


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

DouweMeer Responsive Resident
Responsive Resident

Re: set Multiple values in visual filter

@powerbisach 

Depends a bit what kind of behavior you expect. Do you expect a Yes in the 'Country' table when one of the records in 'User' does have this [country] value? 

Selectedvalue = DDD
  
CountryCheck
INNo
MUYes
PUYes
blankNo

Like this?

powerbisach Helper I
Helper I

Re: set Multiple values in visual filter

 
 
 
 
 Yes...Based on user country field the visual should display details from country table related to it. This can not be done using RLS (due to business constraints) and need to apply visual filter only.
powerbisach Helper I
Helper I

Re: set Multiple values in visual filter

 

It's a simple formula applied in measure user country =

IF(VALUES(user[country])=VALUES(country[country]),"Yes","No")
Microsoft v-jayw-msft
Microsoft

Re: set Multiple values in visual filter

Hi @powerbisach ,

 

Try use SELECTEDVALUE() function instead of VALUE() function.

Like:

if(selectedvalue(user[country])=selectedvalue(country[country]),"Yes",blank)

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

DouweMeer Responsive Resident
Responsive Resident

Re: set Multiple values in visual filter

@v-jayw-msft and @Greg_Deckler 

@Stachu , I saw you were discussing blanks in an earlier post. Perhaps you know this one. 

To not Hijack PowerBisach post I created another post :). 

https://community.powerbi.com/t5/Desktop/Implicit-filters-remove-all-records/m-p/945184#M452965

powerbisach Helper I
Helper I

Re: set Multiple values in visual filter

 v-jayw-msft - The 2 tables are disconnected. Your solution works when i set up a relationship between these 2 tables. When there is no relationship the visual shows no result. 

DouweMeer Responsive Resident
Responsive Resident

Re: set Multiple values in visual filter

@powerbisach 

Do you also get "No" values when the statement doesn't return "Yes"?

Microsoft v-jayw-msft
Microsoft

Re: set Multiple values in visual filter

Hi @powerbisach ,

 

Modify the measure like:

Measure = if(SELECTEDVALUE(Country[Country]) in VALUES(User[Country]),"yes","no")

Result would be shown as below.

3.PNG

4.PNG5.PNG 

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors