Forum Discussion

Kostas's avatar
Kostas
Helper IV
5 years ago
Solved

Vlook Up and Textjoin with DAX Syntax

Hello, 

 

I was wondering if you can help me with the following issue. 

I currently have detailed table with a lot information which includes a column with ID's and a column with Countries among others like the below:

IDCountryNames
U01Australia##
U01Germany##
U01Australia##
U02Italy##
U02United Kingdom##
U03United Kingdom##
U03Spain##
U04Poland##

 

Now, I have created with DAX syntax a new table (Listed Countries) that will return the unique list in a column of the ID's available in the first table (Countries) with the following code:

Listed Countries =
FILTER(DISTINCT('Countries'[ID]), NOT(ISBLANK('Countries'[ID] )))
 
Now the output which I need, in excel I would be a using a combination of VlookUp and textjoin as to bring all the available countries for each ID in a single cell separated by comma like the below, but I am not sure how to achieve the outcome in PowerBI with dax syntax.
 
IDCountries
U01Australia, Germany
U02Italy, United Kingdom
U03United Kingdom, Spain
U04Poland

 

Any suggestions as to how to achieve the above outcome?

 

Thanks

Need Help

6 Replies

    • Kostas's avatar
      Kostas
      Helper IV
      5 years ago

      Hello AllisonKennedy  and thanks for your reply.

      I have already explored the option of using the ConcatenateX function but the issue that I am having is that it returns all the times that a value appears in the countries column instead the unique values. 

      For example, when I am using it, based on the example below for the ID U01, I will get the following outcome:

      ID          Countries

      U01       Australia, Germany, Australia.

       

      Is there a way to return me the unique list (or distinct) of values within the single cell as following:

      ID          Countries

      U01       Australia, Germany

       

      Thanks

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago

        Kostas  It should work if you use CONCATENATEX on the distinct country list. Can you share sample file or at least the DAX and data model screenshot? 

         

        You may need to create a DimCountry table that lists each country only once, then use CONCATENATEX on that table, and filter for ID IN ListedCountries[ID]

         

        Does that make sense?