Table Relationships: show only data in both tables

[Olia]

Hi everyone,

I have 2 tables: Headcount and Country names.

Country names is basically a connection to this page: https://www.worldatlas.com/aatlas/ctycodes.htm, so it has Country Name and 2 letter Code

Headcount... has the 2 letter country Codes, and as you can imagine, it has many rows with the same country code. 

I really want to make a slicer that will show Country Names for people to select.... but when I make a slicer with Country Name variable, it shows ALL country names that are listed on the webpage. I'd like it to show ONLY the country names for which I have a code in Headcount table.

edit: I don't think that the sample-dataset provided to me contains ALL the country codes that we have. so if anotehr user has more permissions, then it'd be nice if he could see the data for the countries that is unavailble to me as the maker of the report.

Any suggestions?

[Greg_Deckler · Accepted solution]

Well, one way would be to create a table that did something like:

Slicer Table = 
VAR __tmpTable = DISTINCT('Headcount'[Code])
RETURN ADDCOLUMNS(__tmpTable,"Country",LOOKUPVALUE('Country'[Country Name],'Country'[Code],'Headcount'[Code]))

Something like that.

[Olia]

I'm noobing something wrong: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

what does this mean?

[Greg_Deckler]

Hmm, difficult to say. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, see attached PBIX.

[Olia]

I tried to make a measure instead of a table... (never made Tables before, so didn't even see that option there).

so of course that didn't work, lol.

Thank you very much for your help and your patience Greg!