topic Re: Grouping Columns in DAX Commands and Tips

Grouping Columns

Anonymous — Wed, 04 Jan 2023 12:59:18 GMT

I've got a table that organizes like the following:

Date	Type	A1	A2	B1	B2	B3
1/1/2022	Red	2	3	1	3	3
1/2/2022	Blue	3	2	2	0	2
1/3/2022	Green	2	3	3	3	1

I need to create a table that groups together the A and B columns so it looks like the following:

Date	Type	A	B
1/1/2022	Red	5	6
1/2/2022	Blue	5	2
1/3/2022	Green	5	7

How would I do this in DAX?

Thanks!!

Re: Grouping Columns

tamerj1 — Wed, 04 Jan 2023 13:06:26 GMT

Hi Anonymous
Is this a source table or a matrix visual? Are you looking for a measure or a calculated table?

Re: Grouping Columns

Anonymous — Wed, 04 Jan 2023 13:16:41 GMT

This is a source table, and I'm looking to create a calculated table. Thanks!

Re: Grouping Columns

Anonymous — Wed, 04 Jan 2023 13:21:42 GMT

Are these columns name (Ax, Bx) going to be fixed or dynamic?

Why not Power Query?

Re: Grouping Columns

Anonymous — Wed, 04 Jan 2023 13:28:29 GMT

I have no problem with PQ, I just started the process in DAX, so that was the direction I figured I'd ask for. But if you know the solution using PQ, please let me know!

Re: Grouping Columns

Anonymous — Wed, 04 Jan 2023 13:39:01 GMT

Here's PQ:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJS0lEKSk0BkiCWMRAbQmljpVgdkDIjmDKnnNJUqJwRFBuAaYg6Y5g696LU1DwkA43hBsfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, A1 = _t, A2 = _t, B1 = _t, B2 = _t, B3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date", "Type"}, "Attribute", "Value"),
#"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}, {"Value", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Type", "Attribute.1"}, {{"Total", each List.Sum([Value]), type number}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute.1]), "Attribute.1", "Total", List.Sum)
in
#"Pivoted Column"

Re: Grouping Columns

FreemanZ — Wed, 04 Jan 2023 13:40:27 GMT

hi Anonymous

not sure if i fullly get you. you can create a table like this:

SumTable2 = SELECTCOLUMNS( data, "Date", data[Date], "Type", data[Type], "A", [A1]+[A2], "B", [B1]+[B2]+[B3] )

i works like this: