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.
My query has the following data:
Account Name | Month | Value 1 | Value 2 | Value 3 | Value 4 | Value 5 |
Company A | 7/1/2020 | 5 | 3 | 2 | 11 | 10 |
Company A | 8/1/2020 | 12 | 40 | 55 | 22 | 44 |
Company A | 9/1/2020 | 2 | 60 | 33 | 37 | 55 |
Company A | 10/1/2020 | 19 | 33 | 20 | 50 | 22 |
Company A | 11/1/2020 | 45 | 22 | 44 | 11 | 7 |
Company A | 12/1/2020 | 4 | 11 | 770 | 900 | 3 |
Company A | 1/1/2021 | 7 | 1 | 33 | 7 | 8 |
Company A | 2/1/2021 | 90 | 9 | 63 | 4 | 98 |
Company A | 3/1/2021 | 10 | 4 | 4 | 4 | 8 |
Company A | 4/1/2021 | 22 | 8 | 6 | 8 | 4 |
Company A | 5/1/2021 | 40 | 8 | 78 | 99 | 7 |
Company A | 6/1/2021 | 2 | 4 | 9 | 22 | 8 |
Company B | 7/1/2020 | 5 | 3 | 2 | 11 | 10 |
Company B | 8/1/2020 | 12 | 40 | 55 | 22 | 44 |
Company B | 9/1/2020 | 2 | 60 | 33 | 777 | 464 |
Company B | 10/1/2020 | 19 | 33 | 20 | 50 | 22 |
Company B | 11/1/2020 | 45 | 22 | 44 | 11 | 7 |
Company B | 12/1/2020 | 4 | 11 | 77 | 9 | 3 |
Company B | 1/1/2021 | 7 | 1 | 33 | 7 | 8 |
Company B | 2/1/2021 | 123 | 599 | 63 | 4 | 98 |
Company B | 3/1/2021 | 10 | 4 | 4 | 4 | 8 |
Company B | 4/1/2021 | 22 | 8 | 6 | 8 | 4 |
Company B | 5/1/2021 | 40 | 8 | 78 | 99 | 7 |
Company B | 6/1/2021 | 2 | 4 | 9 | 22 | 8 |
The end result I need is the Max value for each of the 5 value columns for each Account, but I also need the Month of the Max value for each Value column. I've tried the Grouping option and it can give me the max for all 5 broken down by Account Name, but I can't figure out how to get it to show the Month field related to the Max value for each of the 5 value columns, per Account Name.
Any suggestions?
Solved! Go to Solution.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9c8MgDIb/io85d0GAjRmjzu3YxZfBl8tdh8bukAz590VgPlq1aRkwku95JQHvNInD6bTelmv3Ml/OYiee1+X65vfX+f127iBHKkc6RyZHvTjuJvG0Xj7m5d4d/H+7h72SSvqw94tEVAKoIkiGjwUH4kwQklKF1DCFKwoiBgo0tdE2Sr8LQFY9XKLjhDI2YhIoElMPsx3EcoWqFBmzlDoZRuSSqIj1fJomo2RktCq0C1Xp7Hrr5jivCw9pqLQ4bQodjjpS9W3nT9AXOrwXUZY+zv14OUNVPU1cOtU0tjkImx2EjxxkLd29Gbik0UPY7CH83UPbbWnO/9tA+NVAoAjr3SMPYZOHsMlD2Ogh/NtDx08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type date}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}, {"Value 3", Int64.Type}, {"Value 4", Int64.Type}, {"Value 5", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account Name", "Month"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Account Name", "Attribute"}, {{"AllRows", each _, type table [Account Name=nullable text, Month=nullable date, Attribute=text, Value=number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Max(_, {"Value"})}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Month", "Value"}, {"Month", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Month", type date}, {"Value", Int64.Type}})
in
#"Changed Type1"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9c8MgDIb/io85d0GAjRmjzu3YxZfBl8tdh8bukAz590VgPlq1aRkwku95JQHvNInD6bTelmv3Ml/OYiee1+X65vfX+f127iBHKkc6RyZHvTjuJvG0Xj7m5d4d/H+7h72SSvqw94tEVAKoIkiGjwUH4kwQklKF1DCFKwoiBgo0tdE2Sr8LQFY9XKLjhDI2YhIoElMPsx3EcoWqFBmzlDoZRuSSqIj1fJomo2RktCq0C1Xp7Hrr5jivCw9pqLQ4bQodjjpS9W3nT9AXOrwXUZY+zv14OUNVPU1cOtU0tjkImx2EjxxkLd29Gbik0UPY7CH83UPbbWnO/9tA+NVAoAjr3SMPYZOHsMlD2Ogh/NtDx08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type date}, {"Value 1", Int64.Type}, {"Value 2", Int64.Type}, {"Value 3", Int64.Type}, {"Value 4", Int64.Type}, {"Value 5", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account Name", "Month"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Account Name", "Attribute"}, {{"AllRows", each _, type table [Account Name=nullable text, Month=nullable date, Attribute=text, Value=number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"AllRows", each Table.Max(_, {"Value"})}}),
#"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"Month", "Value"}, {"Month", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded AllRows",{{"Month", type date}, {"Value", Int64.Type}})
in
#"Changed Type1"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks Pat, that did it!!
@Anonymous ,
Column like this for each column ?
flag =
var _max = maxx(filter(Table, [Account Name] = earlier([Account Name])), [Value1])
return
if([Value1] =_max, true(), false())
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |