Select and search countries in multiple coumns in a table

[AliceFGX]

Hello,

I want to create a powerBI report that I will share with users that need to search our sales "activity" with different countries (sales to a customer in the country, sales delivered in the country, sales whose final consumer is in that country).  I wanted for them to be able to select 1 or more countries on a slicer, and then the table showing the three columns of activities (sales to a customer in the country, sales delivered in the country, sales whose final consumer is in that country) and the column of sales to update according to the choice.

For instance: if the user selects France and Brazil, then the table shows all the rows that show either one of these countries on any of the 3 columns and the sales, like this :

Customer country / delivery country/ final consumption country/ Sales

Brasil/ Brasil/ Brasil/100

Peru/Brasil/Peru/200

France/ Italy/Greece/200

Greece/Greece/France/50

Italy/Italy/France/300

and the row Greece/Greece/Greece/700 for example would not appear

I tried to use power query to make 3 identical queries where in each I filtered one of the columns based on a parameter then merge them into one query and suppressed double rows. But it is too complicated for the users to change the parameter on the dataset, and I would have to add many parameters to allow choosing many countries.  I guess the solution must be in DAX, or liking tables on the model but I don't know how to do it. Any ideas?

Thank you very much for your help !

[AliceFGX]

Hi @v-jayw-msft ,

Thanks for your quick answer! I created a list of possible countries called ctrys that I used on the slicer and on [slicer] in your formula and even though I did not link the two tables in the model it worked !

you've brightened my day ! 🙂

Best Regards,

Alice

[v-jayw-msft · Accepted solution]

Hi @AliceFGX ,

Create a measure like:

measure = if(selectedvalue([customer country]) in values([slicer])|| selectedvalue([delivery country]) in values([slicer])|| selectedvalue([final country]) in values([slicer]), 1, 0)

Then add this measure to visual filter and set value = 1.

Best Regards,

Jay