Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
hi @Rinat
Try this event, Is this your result?
measure =
IF(
[Value1] = [Value2], [Value],
COMBINEVALUES(";", [Value1],[Value2])
)
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"
@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 1 | Value 2 | Value 3 |
so I'm wondering what is "Value" in your formula. Thank you!
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
@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.
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])
))))
Hi @DimaMD. I'll keep your solution in a back pocket and will test it when I can. Thank you for your help!
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
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 _ <> "")), " ;"))
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
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?
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:
Proud to be a Super User!