Forum Discussion
DAX Return Two Columns With Distinct A Column And Concatenated Distinct B Column Values
Hi NTXDallasUser ,
You can achieve this in Power Query.
First you can remove the duplicate rows (where the id and the name are the same).Then you group by on the id, select Sum for the name column, and when it shows you the error do a little trick based on this blog post's 3rd chapter and replace the List.Sum with Text.Combine and specify the separator.
Here is the full M code with your sample data:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrydPZWitWJVjICcrz8XcFsYyDbyd8JzDYBsl38/Rx9XMBckB4XxzBXOAdugCmQ4+ji6KsUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalespersonNum = _t, SalespersonName = _t]), #"Removed Duplicates" = Table.Distinct(Source), #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"SalespersonNum", Int64.Type}, {"SalespersonName", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SalespersonNum"}, {{"SalespersonS", each Text.Combine([SalespersonName],", "), type nullable text}}) in #"Grouped Rows"
Let me know if you have any questions.
Super UserHi NTXDallasUser ,
You can achieve this in Power Query.
First you can remove the duplicate rows (where the id and the name are the same).
Then you group by on the id, select Sum for the name column, and when it shows you the error do a little trick based on this blog post's 3rd chapter and replace the List.Sum with Text.Combine and specify the separator.
Here is the full M code with your sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrydPZWitWJVjICcrz8XcFsYyDbyd8JzDYBsl38/Rx9XMBckB4XxzBXOAdugCmQ4+ji6KsUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SalespersonNum = _t, SalespersonName = _t]),
#"Removed Duplicates" = Table.Distinct(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"SalespersonNum", Int64.Type}, {"SalespersonName", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SalespersonNum"}, {{"SalespersonS", each Text.Combine([SalespersonName],", "), type nullable text}})
in
#"Grouped Rows"
Let me know if you have any questions.
I really like this idea, however the reason I mentioned this needing to be in DAX, is becuase I'm actually doing this work over in Power BI Report Builder, and targeting a Power BI Semantic model which I don't have access to change. Power BI Report Builder is pretty limited, but it fits the use case that the underlying project requires (a paginated report auto-sent at a specific interval). In case I can't get access to change the semantic model, any ideas for doing this with DAX? From doing some testing work on this I know I need to create at least one measure, and combine that with table output, but I'm only versed in basically DAX 'one-liners' and this is well beyond my level of sophistication.
- dk_dk
Hi NTXDallasUser
I have a solution in DAX as well, (there is probably a more elegant way to do it though):
Salesperson Original is your original Input table.You can create the following calculated table:
Salesperson DAX = ADDCOLUMNS( 'Salesperson Original', "SalespersonS", CONCATENATEX( FILTER(DISTINCT('Salesperson Original'), 'Salesperson Original'[SalespersonNum] = EARLIER('Salesperson Original'[SalespersonNum])), 'Salesperson Original'[SalespersonName], ", " ) )
And when you add the right columns to a visual the result looks like this:
I hope this helps!
Best regards,
Daniel