Forum Discussion
DAX Return Two Columns With Distinct A Column And Concatenated Distinct B Column Values
I have a table with multiple columns, but two specific columns in question. I am stuck trying to write a DAX expression to create two columns from this data example. Column Salesman, is not unique on the table, nor is Name. I am trying to get the following returned as two columns. Any thoughts?
SalespersonNum SalespersonName
1 RICK
2 JOE
3 BOB
4 DONALD
1 DAVE
1 RICK
5 ADAM
RETURN FOLLOWING:
SalespersonNum SalespersonNameS
1 RICK, DAVE
2 JOE
3 BOB
4 DONALD
5 ADAM
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.
6 Replies
- dk_dk
Super User
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.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
I was able to get accesss to the semantic model, and that m code works perfectly. Thanks!
- AlexTheGreat
Resolver II
Hi,
I used your "data" and came up with this solution:SalesPersonNameS = CONCATENATEX(SalesPerson,SalesPerson[SalesPersonName],", ")Let me know if it works!Thank you for your reply, however I think the table may not hgave been easy enough to see - I need two columns returned searching two columns of input. On the output column SalespersonNum must be DISTINCT. The SalesPersonNames column would concatenate if needed any SalesPersonName (only Name) in conditions where the same SalesPersonNum contains unique SalesPersonName values. Here's the use case, and that might help, along with a table version of the input and output: Salesperson numbers can be recycled over time such that the same salesperson number could have a different name. The salesperson number is being used as a filter, but I want the name value to show ALL salespeople NAMES in the returned column that have had that number. Notice on the input, the SalespersonNum can appear with the SAME VALUE and either the SAME or DIFFERENT SalespersonName.
INPUT COLUMNS:
SalespersonNum SalespersonName 1 RICK 2 JOE 3 BOB 4 DONALD 1 DAVE 1 RICK 5 ADAM DESIRED OUTPUT (Both Columns)
SalespersonNum SalespersonNameS 1 RICK, DAVE 2 JOE 3 BOB 4 DONALD 5 ADAM
