Lookup to return multiple values

[iKettle]

Hey Guys, I have two tables, Table One which has the list of countries and the other Table 2 has the countriesid amongst other fields. But within Table 2 the countriesid field, there are multiple ids, so for that field I could have 1, 2, 5 or 5, 2 of what countries have been selected. I am trying to get the country names into a new column using LOOKUP but it only returns the first country match and not the others. What formula would I use to return the list of countries within the new column separated by comma? or what would be the best approach in this scenario?

I'm pretty new at BI, so step by step instructions will be great 🙂 

[Greg_Deckler]

@iKettle Use CONCATENATEX(FILTER(...), ..., ", ")

[iKettle]

Hey Guys, any assistance will be appreciated. What I am trying to get is the lookup values of the countriesid column where there is more than one id (see table below). The values from from another table called MasterCountryHRControlsFocals[Id] when lookup is used to return MasterCountryHRControlsFocals[Title]. The issue I have is that I only get the first of  value MasterCountryHRControlsFocals[Title], so its not retuning the other values in the MasterCountryHRControlsFocals[Title]. As you can see below, the 3rd last row has multiple ids 253, 250, 215. But is only return the first country title and not the other two. How can I fix this?

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and clealry show the expected result.  Also, do you want a measure or a calculated column?

[iKettle]

I have tried this

Countries = CONCATENATEX(Filter(MasterCountryHRControlsFocals, MasterCountryHRControlsFocals[Id] = 'Audit Tracker List (2)'[CountriesId]), MasterCountryHRControlsFocals[Title], [Title], ",")

But it still only gives me one value. In the lookup, I have extracted the value in the field 'Audit Tracker List (2)'[CountriesId], so it has 234, 250, 190 etc....But using the above formula I only get the first country name and not the rest.