How to concatenate unique values?

[Rinat]

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

[DimaMD]

hi @Rinat 
Try this event, Is this your result?

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

[Rinat]

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?

[DimaMD]

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

[Rinat]

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

[DimaMD]

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

[Rinat]

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!

[DimaMD]

@Rinat 

the measure is, 

measure = 

IF(

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

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

)

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

[Rinat]

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!

[DimaMD]

Hi, @Rinat  Yes, the result will be as follows

[Rinat]

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

[DimaMD]

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?

[Rinat]

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

[DimaMD]

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])
            ))))

[Rinat]

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

[v-shex-msft · Accepted solution]

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

[Rinat]

thank you, @v-shex-msft ! 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 _ <> "")), " ;"))

[v-shex-msft]

Hi @DimaMD,

In fact, the first _ operator in Table.AddColumn function means the current row so I used the Record.ToList(_) to transform the current row values as a list. (power query use 'record' format to store table row)

M Language Operators - PowerQuery M | Microsoft Docs

If you want to exclude or only check specific fields, you can nest a 'Record.SelectFields' function in it to pick up specific fields. 

Record.SelectFields - PowerQuery M | Microsoft Docs

Here is the sample and it only works on the 'column1,column2,column3' that I defined in the function: (I try to change the codes styles to more readable format)

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

Regards,

Xiaoxin Sheng

[ValtteriN]

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?

[Rinat]

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!

[ValtteriN]

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: