CODEWITHSUNDEEP
sqlpowerbidax
user13438474
user13438474

Reputation:

How can we group by one column and then need to group other column values in comma separated in DAX

Eg:

Department Customer Name
CSE A
CSE B
IT D
CSE A
ECE C
EEE B
ECE F

Output:

Department Customer Name
CSE A,B
IT D
ECE C,F
EEE B

Upvotes: 2

Views: 227

Answers (3)

Johnny Spindler
Johnny Spindler

Reputation: 456

Try this for the selectedvalue as a VAR:

Measure =
VAR MyDepartment = SELECTEDVALUE(TheTable[Department])
RETURN
CONCATENATEX(
    FILTER(
        TheTable,
        TheTable[Department] = MyDepartment
    ),
    TheTable[Customer Name],
    ","
)

Upvotes: 0

smpa01
smpa01

Reputation: 4346

Try this DAX measure

Measure =
CONCATENATEX ( VALUES ( tbl[Customer] ), tbl[Customer], "," )

s1

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can do it in Power Query by adding these below 5 steps to your table's Advance Query Editor

Please adjust the PREVIOUS_STEP_NAME in the code as per your last step name.

let
    //............,
    //............ all previous steps,
    
    #"Removed Duplicates" = Table.Distinct(#"PREVIOUS_STEP_NAME"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Department"}, {{"all name", each _, type table [Department=nullable text, Customer Name=nullable text]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [all name][Customer Name]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"all name"})
in
    #"Removed Columns"

Here is the output-

enter image description here

But, if you wants to keep your base table unchanged, just create a new table pointing the base table as source and perform those above steps.

Upvotes: 0

Related Questions