Forum Discussion

iKettle's avatar
iKettle
Frequent Visitor
4 years ago

Lookup to return multiple values

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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

  • iKettle's avatar
    iKettle
    Frequent Visitor
    4 years ago

    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.
  • iKettle's avatar
    iKettle
    Frequent Visitor
    4 years ago

    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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      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?