topic Re: How to concatenate unique values? in DAX Commands and Tips

How to concatenate unique values?

Rinat — Thu, 20 Jan 2022 12:51:02 GMT

Hello everyone.

Can someone please help me to get the DAX formula to create DAX formula to create a custom column which contains concatenated values from other columns with "; " as the delimiter. I just started my DAX journey and tried to use COMBINEDVALUES but don't know how to make them contain unique values. I'm looking for a DAX substitute of =TEXTJOIN(", ",TRUE, UNIQUE(G3:I3,TRUE)).

Hope it makes sense 🙂

Thank you very much in advance.

Example:

Value 1	Value 2	Value 3	Result
John	John	John	John
Marie	Alex	Alex	Marie; Alex

Re: How to concatenate unique values?

ValtteriN — Thu, 20 Jan 2022 13:13:00 GMT

Hi,

Getting a distinct list of values like this would be really straightforward using powerquery. Might I inquire what is your end goal after the data is in this format as described in your post?

Re: How to concatenate unique values?

DimaMD — Thu, 20 Jan 2022 13:33:29 GMT

hi Rinat
Try this event, Is this your result?

measure = IF( [Value1] = [Value2], [Value], COMBINEVALUES(";", [Value1],[Value2]) )

Re: How to concatenate unique values?

Rinat — Thu, 20 Jan 2022 13:47:57 GMT

Thank you for this, I'll try it today or tomorrow when I have access. What if there are 3 or more values I need to concatenate?

Re: How to concatenate unique values?

Rinat — Thu, 20 Jan 2022 13:50:39 GMT

Hi. If this would be easier to do in power query, I can certainly do this there, especially as I learned that performance is better while using it. If you can give me an example for 3 values as above and what if I have more than 3. Thank you a lot!

Re: How to concatenate unique values?

ValtteriN — Thu, 20 Jan 2022 14:00:55 GMT

Hi,

For using powerquery I would unpivot the columns in question and use remove duplicates on the unpivoted column. This doesn't provide you with a list with separator as you described and that is the reason I asked what is your end goal. Here you example data is names so I guess you want a list of names?

In that case PQ will be sufficient. However, if you insist on a list with separator COMBINEVALUES is a good option.

Some examples of what I mean:

Re: How to concatenate unique values?

DimaMD — Thu, 20 Jan 2022 14:17:42 GMT

Rinat ,
You just need to add another column, in my example "Value3"

Re: How to concatenate unique values?

Rinat — Thu, 20 Jan 2022 14:59:08 GMT

DimaMD what would be the "Value" string in your formula?

Re: How to concatenate unique values?

DimaMD — Thu, 20 Jan 2022 15:29:58 GMT

Rinat
I do not quite understand, give an example of data

Re: How to concatenate unique values?

Rinat — Thu, 20 Jan 2022 16:40:56 GMT

I'm sorry if I didn't make it clear. In my example there are

Value 1

Value 2

Value 3

so I'm wondering what is "Value" in your formula. Thank you!

Re: How to concatenate unique values?

DimaMD — Thu, 20 Jan 2022 18:41:26 GMT

Rinat

the measure is,

measure =

IF(

[Value1] = [Value2], [Value3],

COMBINEVALUES(";", [Value1],[Value2])

)

I use the 'IF' function to compare "Values"

Re: How to concatenate unique values?

Rinat — Fri, 21 Jan 2022 10:25:19 GMT

Thank you, DimaMD. Is it possible to eliminate delimiter if, for example, Value1 is empty? In my case if Value1 is empty, I'm getting ";Value2;Value3". Thanks a lot!

Re: How to concatenate unique values?

DimaMD — Fri, 21 Jan 2022 14:18:50 GMT

Hi, Rinat Yes, the result will be as follows

Re: How to concatenate unique values?

Rinat — Mon, 24 Jan 2022 13:12:57 GMT

DimaMD unfortunately, after some testing, I cannot confirm the formula worked for me. In your screen above I expected to see Tj,Ta. However, I see only Ta in the last row? Can you please explain how your formula works? Thank you very much for your help.

Re: How to concatenate unique values?

DimaMD — Mon, 24 Jan 2022 14:46:59 GMT

hi Rinat
Using the IF function, I compare values 1 and values 2 and assign values3, otherwise I order COMBINE VALUES (";", [Value1], Value2])
example
if John = John , John
if Marie = Alex, COMBINEVALUES(";", [Marie], [Alex])
result
John
Marie;Alex
I hope you understand

I advise you to familiarize yourself with this feature
https://docs.microsoft.com/en-us/dax/if-function-dax
https://dax.guide/if/
Your task is complicated if you need to compare other Values, how many Values do you have?

Re: How to concatenate unique values?

Rinat — Mon, 24 Jan 2022 15:17:02 GMT

DimaMD you are right. I need to learn more and I'm doing it.

I have no more than 4 Values in total and different combinations of filled-in and blank fields. In most cases, I have 3 values filled in but I don't want to hardcode this logic to the formula if possible. I have very little programming experience but maybe it's possible to read values in a cycle 4 times and add value to the result if the previous value doesn't equal the current. Just an idea...

Re: How to concatenate unique values?

Anonymous — Tue, 25 Jan 2022 01:44:17 GMT

Hi Rinat,

According to your description, I think the Dax function doest not suitable analyze multiple fields. Perhaps you can try to use Power query formulas to do this operation.

I create a custom column with M query functions to extract and analyze current row field values, concatenate 'nonblank' and distinct values with character ";":

#"Added Custom" = Table.AddColumn(#"Changed Type", "Combine", each Text.Combine(List.Distinct(List.Select(Record.ToList(_), each _ <> "")),";"))

Full query:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSQcKxOtFKTkAWDENEnIEsIHIGY5CAC5DlAhF0VYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Combine", each Text.Combine(List.Distinct(List.Select(Record.ToList(_), each _ <> "")),";")) in #"Added Custom"

Regards,

Xiaoxin Sheng

Re: How to concatenate unique values?

DimaMD — Tue, 25 Jan 2022 15:00:57 GMT

HI Rinat try it

Result = IF( [Value1] = [Value2],[Value3], IF( [Value1] = BLANK() && [Value2] <> [Value3], COMBINEVALUES(";", [Value2],[Value3]), IF([Value2] = BLANK() && [Value1] <> [Value3], COMBINEVALUES(";", [Value1],[Value3]), IF([Value3] = BLANK() && [Value1] <> [Value2], COMBINEVALUES(";", [Value1],[Value2]), IF([Value1]<> BLANK() && [Value2]<> BLANK()&& [Value3] <> BLANK()&& [Value1]<>[Value2], COMBINEVALUES(";", [Value1],[Value2]), [Value3]) ))))

Re: How to concatenate unique values?

Rinat — Wed, 26 Jan 2022 11:53:58 GMT

thank you, Anonymous ! It looks like it worked as expected. As I'm new, let me ask you the following: would it be correct to pass the list in the following way?

List.Select({[column1],[column2],[column3]})List.Select({[column1],[column2],[column3]})

I've edited the code manually in the advanced editor. What if I want to add column via wizard?

For some reason the following didn't work for me:

=Text.Combine(List.Distinct(List.Select({[column1],[column2],[column3]}, each _ <> "")), " ;"))

Re: How to concatenate unique values?

Rinat — Wed, 26 Jan 2022 11:56:49 GMT

Hi DimaMD. I'll keep your solution in a back pocket and will test it when I can. Thank you for your help!