Vlook Up and Textjoin with DAX Syntax

[Kostas]

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:

ID	Country	Names
U01	Australia	##
U01	Germany	##
U01	Australia	##
U02	Italy	##
U02	United Kingdom	##
U03	United Kingdom	##
U03	Spain	##
U04	Poland	##

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.

ID	Countries
U01	Australia, Germany
U02	Italy, United Kingdom
U03	United Kingdom, Spain
U04	Poland

Any suggestions as to how to achieve the above outcome?

Thanks

[Kostas · Accepted solution]

Hello, 

I actually managed to find a solution within this topic

Solved: Concatenate only unique strings in measure - Microsoft Power BI Community

Thanks

[AllisonKennedy]

@Kostas  The DISTINCT function in DAX already removes the blank row created by relationships, so you may not need such a long formula for that table. 

https://docs.microsoft.com/en-us/dax/distinct-function-dax 

That aside, the CONCATENATEX function is likely what you're looking for here: 

https://excelwithallison.blogspot.com/2021/03/data-story-of-month-employee-form.html

Very similar to the scenarion in my blog above.

[Kostas]

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]

@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?

[Kostas]

Hello @AllisonKennedy 

I have created with DAX my main table which is the following:

Countries List =
FILTER(DISTINCT('Countries'[ID]), NOT(ISBLANK('Countries'[ID] )))

That returned to me an one column table with the distinct list of ID's from the "Countries" table. 

Now I need to create multiple columns which they will contain all the values related to the specific ID in each row. One example is the "Countries" column. 
The desired outcome will be the below 
Original Table:
ID       Countries          Rating
U01     Australia           Effective
U01     Spain               Not Effective
U02     Australia         Effective
U02     Australia          Improvement Required
U03     Germany        Not Effective
U02     Italy                 Effective

Desired Outcome:

ID          Countries                   Ratings
U01       Australia, Spain         Effective, Not Effective
U02       Australia, Italy          Improvement Required, Effective
U03       Germany                   Not Effective

At the moment, if after I create the distinct table use the ConcatenateX function to create the "Countries" Column, my code will be the following:

Countries =
CONCATENATEX(
'Countries',
LOOKUPVALUE(
'Countries'[Countries], 'Countries'[ID], 'Country List'[ID] ) , ", ", 'Countries'[ID]
)

but the outcome for the specific column is the following:

ID           Countries
U01        Australia, Spain
U02        Australia, Australia, Italy
U03         Germany

As you can see, for the countries in the row with the ID "U02" the function does not return the distinct values within the specific cell but all the values related to the ID. That means that if in my original table for the ID "U02" the value "Australia" appears in 20 rows, I will get into the single cell the value "Australia" twenty times instead of one.

Does that makes sense?

[AllisonKennedy]

@Kostas Sorry for the delayed reply and glad you got this sorted! I'm assuming your Countries table has more than one row per country? You need to provide it a UNIQUE list (I usually do something like using the VALUES function if I don't have a lookuptable).