concatenate without duplication based on the multiple columns (DAX)

[Saxon10]

Hi,

I have 3 columns are Area, country and code and those three columns data type is mixed (Text and Number together)

I would like to concatenate country and code column together in column without duplication based on the area column.

I am looking for new calculated column option.

AREA	COUNTRY	CODE	RESULT
DD1	IND	001	IND/001,PAK/008
DD1	IND	001	IND/001,PAK/008
DD1	IND	001	IND/001,PAK/008
DD1	IND	001	IND/001,PAK/008
DD1	PAK	008	IND/001,PAK/008
DD1	PAK	008	IND/001,PAK/008
DD2	SRI	DMK	SRI/DMK
DD3	AUS	101	AUS/101,USA/234
DD3	AUS	101	AUS/101,USA/234
DD3	USA	234	AUS/101,USA/234
DD3	USA	234	AUS/101,USA/234

I am applying the following DAX 

Result1 = CALCULATE(CONCATENATEX('Table','Table'[COUNTRY]&'Table'[CODE],","),FILTER('Table','Table'[AREA]=EARLIER('Table'[AREA]))) but it's giving duplication value.

Any advise please.

[parry2k · Accepted solution]

@Saxon10 try this code to add a calculated column

Result1 = 
VAR __t = 
SUMMARIZE (
    FILTER ( 'Table', 'Table'[AREA] = EARLIER ( 'Table'[AREA] ) ), 
    'Table'[AREA], 'Table'[COUNTRY], 'Table'[CODE], 
    "@Country", CALCULATE ( MAX ( 'Table'[COUNTRY] ) ), 
    "@Code", CALCULATE ( MAX ( 'Table'[CODE] ) ) 
)
RETURN
CONCATENATEX (  __t , [@Country] & "/" & [@Code], "," )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Ashish_Mathur]

Hi,

This can be done quite easily in the Query Editor.  Are you interested in a Query Editor solution?

[Saxon10]

Hi,

Thanks for your reply.

Yes, Please. Sometimes I have huge data so power query will help to get the quick results. 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnExVNJR8vRzAZIGBoZKsTrUEAtw9AaLWeARMwLygoM8gaSLrzdUzBjIcwwNBpKGcPNwiYUGOwJJI2MT7GKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Country = _t, Code = _t]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Country", "Code"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Area"}, {{"All Merged", each Text.Combine(List.Distinct([Merged]), ", "), type text}}),
    Joined = Table.Join(#"Merged Columns", "Area", #"Grouped Rows", "Area"),
    #"Split Column by Delimiter" = Table.SplitColumn(Joined, "Merged", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Area", type text}, {"Merged.1", type text}, {"Merged.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Merged.1", "Country"}, {"Merged.2", "Code"}})
in
    #"Renamed Columns"

Hope this helps.

[Saxon10]

Thanks for your help. 

[Ashish_Mathur]

You are welcome.

[parry2k · Accepted solution]

@Saxon10 try this code to add a calculated column

Result1 = 
VAR __t = 
SUMMARIZE (
    FILTER ( 'Table', 'Table'[AREA] = EARLIER ( 'Table'[AREA] ) ), 
    'Table'[AREA], 'Table'[COUNTRY], 'Table'[CODE], 
    "@Country", CALCULATE ( MAX ( 'Table'[COUNTRY] ) ), 
    "@Code", CALCULATE ( MAX ( 'Table'[CODE] ) ) 
)
RETURN
CONCATENATEX (  __t , [@Country] & "/" & [@Code], "," )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Saxon10]

Thanks for your reply and help.

I will check formula and update the feedback to you.