Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have a table with 4 products and the corresponding 4 quantities.
I need to group them (DAX command) as pictured.
Want to use it for a sales funnel
Solved! Go to Solution.
Hi @amiskow
This can be best done in PQ. Place the following M code in a blank query to see the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"P1", "P2", "P3", "P4"}),
T1_ = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
auxT2_ = Table.SelectColumns(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
T2_ = Table.UnpivotOtherColumns(auxT2_, {}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(T2_, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each T1_[Value]{[Index]}, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"Count", each List.Sum([Value]), type number}})
in
#"Grouped Rows"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@amiskow , Dax is not the best way to do it . But please find the DAX
New Table =
Union(
summarize(Table, Table[Produkt_1], Table[Quantity_1]),
summarize(Table, Table[Produkt_2], Table[Quantity_2]),
summarize(Table, Table[Produkt_3], Table[Quantity_3]),
summarize(Table, Table[Produkt_4], Table[Quantity_4])
)
New Table =
var _tab = Union(
summarize(Table, Table[Produkt_1], Table[Quantity_1]),
summarize(Table, Table[Produkt_2], Table[Quantity_2]),
summarize(Table, Table[Produkt_3], Table[Quantity_3]),
summarize(Table, Table[Produkt_4], Table[Quantity_4])
)
return summarize(Table, Table[Produkt_1], "Quantity" , Summ(Table[Quantity_1]))
In the first statement, you can selectcolumns in place summarize.
Refer : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
DAX is not good at processing data like that. M code is better to do this
@amiskow , Power Query is way much competent at such a data cleansing job,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
Transformation =Table.FromColumns(List.Transform(List.Split(Table.ToColumns(Source),4), List.Combine)),
#"Changed Type" = Table.TransformColumnTypes(Transformation,{{"Column2", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Sum", each List.Sum([Column2])}})
in
#"Grouped Rows"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @amiskow
This can be best done in PQ. Place the following M code in a blank query to see the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"P1", "P2", "P3", "P4"}),
T1_ = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
auxT2_ = Table.SelectColumns(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
T2_ = Table.UnpivotOtherColumns(auxT2_, {}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(T2_, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each T1_[Value]{[Index]}, Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"Count", each List.Sum([Value]), type number}})
in
#"Grouped Rows"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB
Thanks for help, but I'm not advanced in this way. I don't know how to do this 😞
Go to the Query editor (Transform data) -> New Source -> Blank query
Select the query just created -> Advanced Editor
This will open a window with M code for the blank query:
let
Source = ""
in
Source
Now delete that code, paste the one I gave you before and clock on Done
On the right side (Applied steps) you'll see the step of the processing. Start by the first one (Source) move ahead one by one to see how it all works. You'll have to adapt it slightly to your data.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thank you very much, it works great : )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |