cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rinat
Helper I
Helper I

How to concatenate unique values?

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 1Value 2Value 3Result
JohnJohnJohnJohn
MarieAlexAlexMarie; Alex
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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 _ <> "")),";"))

9.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

20 REPLIES 20
DimaMD
Helper V
Helper V

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

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

Screenshot_6.jpg

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?

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

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

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

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

Value 1Value 2Value 3

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

@Rinat 

the measure is, 

measure = 

IF(

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

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

)

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

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!

Hi, @Rinat  Yes, the result will be as follows
Screenshot_6.jpg

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

Rinat_0-1643029869227.png

 

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?


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

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

 

Screenshot_6.jpg

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
Community Support
Community Support

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 _ <> "")),";"))

9.png

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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
Community Support
Community Support

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 _ <> ""
                        )
                    ),
                    ";"
                )
        )

 

1.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ValtteriN
Super User
Super User

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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:

ValtteriN_0-1642687208521.png

 

ValtteriN_1-1642687224093.png

 

 

ValtteriN_2-1642687234196.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!