cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CobusEllis Frequent Visitor
Frequent Visitor

How to mark the latest baseline per project

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

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: How to mark the latest baseline per project

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!  

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: How to mark the latest baseline per project

Ah, if this was DAX it would be easy using MAXX. I'm sure that @ImkeF knows of a way in M though.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

LivioLanzo Super Contributor
Super Contributor

Re: How to mark the latest baseline per project

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!  

View solution in original post

Super User I
Super User I

Re: How to mark the latest baseline per project

@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...

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Community Support Team
Community Support Team

Re: How to mark the latest baseline per project

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CobusEllis Frequent Visitor
Frequent Visitor

Re: How to mark the latest baseline per project

Thanks for all the helpful replies!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors