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'm working on data from MS Projects.
A project can have multiple baselines, I need the first and the last.
The first one is easy as the baseline number is 0.
However I'm having trouble building a formula to mark per Project per Baseline what is the latest Baseline Number. With that flag I can add a filter etc.
Current data structure for baselines:
ProjectId, BaselineNumber, (metadata)
Sample data:
Proj1, 0, ...
Proj1, 1, ...
Proj1, 2, ...
Proj2, 0, ...
Proj3, 0, ...
Proj3, 1, ...
I need to add a formula that will enter the data as such:
ProjectId, BaselineNumber, IsLatest, (metadata)
Proj1, 0, 0 ,...
Proj1, 1, 0 ,...
Proj1, 2, 1 ,...
Proj2, 0, 1 ,...
Proj3, 0, 0 ,...
Proj3, 1, 1 ,...
Very Important, this must happen in the Query editor!
Please let me know if you have any questions!
Thank you in advance,
Cobus
Solved! Go to Solution.
You can easily do it like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lEyUIrVQfAMUXhGcJ4RikpjDB5QXywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Baseline = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Baseline", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Mx", each List.Max([Baseline]), type number}}), Custom1 = Table.NestedJoin(#"Changed Type", {"Project"}, #"Grouped Rows", {"Project"}, "Latest", JoinKind.Inner), #"Added Custom" = Table.AddColumn(Custom1, "IsLatest", each if [Latest]{0}[Mx] = [Baseline] then 1 else 0), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Latest"}) in #"Removed Columns"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks for all the helpful replies!
Hi @CobusEllis,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
You can easily do it like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lEyUIrVQfAMUXhGcJ4RikpjDB5QXywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Baseline = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Baseline", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Mx", each List.Max([Baseline]), type number}}), Custom1 = Table.NestedJoin(#"Changed Type", {"Project"}, #"Grouped Rows", {"Project"}, "Latest", JoinKind.Inner), #"Added Custom" = Table.AddColumn(Custom1, "IsLatest", each if [Latest]{0}[Mx] = [Baseline] then 1 else 0), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Latest"}) in #"Removed Columns"
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@CobusEllis If you would like to do it in DAX then,
IsLatest = VAR _maxIndex = CALCULATE(MAX(FlagLatest[Index]),FILTER(ALL(FlagLatest),FlagLatest[ProjectID]=EARLIER(FlagLatest[ProjectID]))) RETURN IF(FlagLatest[Index] = _maxIndex,1,0)
Note - I've created an Index Column in Power Query.
Here is the expected output...
Proud to be a PBI Community Champion
Ah, if this was DAX it would be easy using MAXX. I'm sure that @ImkeF knows of a way in M though.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |