Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a data set that roughly looks like this:
State | Date | Percent Complete |
CT | 1/1/2024 | 25 |
CT | 1/8/2024 | 40 |
MA | 1/1/2024 | 50 |
RI | 1/1/2024 | 10 |
RI | 1/8/2024 | 30 |
RI | 1/15/2024 | 70 |
NY | 1/1/2024 | 100 |
NJ | 1/8/2024 | 20 |
NJ | 1/15/2024 | 50 |
DE | 1/15/2024 | 75 |
It is on a sharepoint list being filled via a MS Form. The states don't always make a report so I need to be able to calculate the overall percent complete of the project based on their highest reported percentage. The calculation I have now seems to be adding them together (for example CT is added to 65 instead of just using the highest reported 40). I thought about using latest date, but some states stop reporting once they hit 100. Is there a way to do this in Power Query Editor vice a DAX command. Thank you in advance.
Solved! Go to Solution.
You can't do it in DAX via the power query editor, but you can group by state and choose the max aggregation for percent complete.
--Nate
DAX:
SummaryTable = SUMMARIZE('Table','Table'[State ],"Max Percent Complete",max('Table'[Percent Complete]))
Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg5RUNJRMtQ31DcyMDIBMo1MlWJ1EOIWMHETA7C4ryOaelOIeJAnmrghqjjcHGM09aYwCXOIhF8khkFQCS80k4xQxREmQZ3k4ophBdBvsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Date = _t, #"Percent Complete" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Date", type date}, {"Percent Complete", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {
{"Max", (t)=>Table.SelectRows(t, each [Percent Complete] = List.Max(t[Percent Complete])),
type table[State=text, Date=date, Percent Complete=Int64.Type]}}),
#"Expanded Max" = Table.ExpandTableColumn(#"Grouped Rows", "Max", {"Date", "Percent Complete"}, {"Date", "Percent Complete"})
in
#"Expanded Max"
DAX:
SummaryTable = SUMMARIZE('Table','Table'[State ],"Max Percent Complete",max('Table'[Percent Complete]))
Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg5RUNJRMtQ31DcyMDIBMo1MlWJ1EOIWMHETA7C4ryOaelOIeJAnmrghqjjcHGM09aYwCXOIhF8khkFQCS80k4xQxREmQZ3k4ophBdBvsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Date = _t, #"Percent Complete" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Date", type date}, {"Percent Complete", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {
{"Max", (t)=>Table.SelectRows(t, each [Percent Complete] = List.Max(t[Percent Complete])),
type table[State=text, Date=date, Percent Complete=Int64.Type]}}),
#"Expanded Max" = Table.ExpandTableColumn(#"Grouped Rows", "Max", {"Date", "Percent Complete"}, {"Date", "Percent Complete"})
in
#"Expanded Max"
You can't do it in DAX via the power query editor, but you can group by state and choose the max aggregation for percent complete.
--Nate
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.