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

MS Project baseline tracking

Dear all,

need Your help on tracking baselines in Projects.

I apologize in advance for the format or mistakes as I'm a total newbie to this "measures" and "tables" world.

1) I want to get into the BPI view and show to BUs if there is any baseline on their Projects at all.

And I used the formula found on some PBI-Project template to add a column with logical test, based on Baseline Finish date (if there is no baseline set, than there is no value in this field or any other baselines field).

 

Baseline status = IF(ISBLANK(RELATED(ProjectBaselines[Project Baseline Finish Date]))=TRUE();"No";"Yes")

 

And I "almost" get what I want:

Baseline view.PNG

 

 

 

However, I've noticed, that it only gets values for "Baseline 0" and PMs not always set the Baseline 0 as first/initial.  So in query editor I found, that there is a filter step, that filters all the rest of the baseline numbers, like 1,2, etc.

 

#"Filtered Rows" = Table.SelectRows(ProjectBaselines_table, each ([BaselineNumber] = 0))

 

If I remove the filter, I get an error:  Column 'Project Id' in Table 'ProjectBaselines' contains a duplicate value '9805f398-2889-ea11-b0a8-00155d54a003' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

The one and only active relationship between the tables is this:

Relationship.png

 

If I select any other relationship with *:1 or *:*, then I don't get that relationship error, but I get an error on my logical test formula for "Baseline status":

 

"The column 'ProjectBaselines[Project Baseline Finish Date]' either doesn't exist or doesn't have a relationship to any table available in the current context. "

Can You point me, please, where and what has to be corrected, to get my "baseline checker" working on all possible baselines not only "0"?

2) When I have the 1) resolved, I would like to calculate Project KPIs based on the "latest"(sometimes, it can be not the highest number) Baseline and Actual numbers: Finish date, work, duration. I would assume, either I would need a date-time when was the latest baseline set. If it is possible at all to get that from Project Online Odata feed?  Or at least find/compare the baselines, that have "latest" finish date. I tried to "Google" this out, but did not found anything at the level of my understanding 🙄.

Thank You in advance for any clues to these!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: MS Project baseline tracking

HI @Tedas,

It seems like you are working with two tables that not contain unique field values.
In my opinion, I'd like to suggest creating a calculated table with dax formulas to extract values from two tables and remove duplicate rows.

Bridge =
DISTINCT (
    UNION ( ALL ( ProjectBaselines[Project Id] ), ALL ( Projects[Project Id] ) )
)

After these steps, you can use this 'bridge' table to link two tables and use bridge table key and raw table fields with 'aggregate' function/methods(e.g. max, min...) to get specific values.

How to join many to many with a bridge table in POWER BI 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: MS Project baseline tracking

HI @Tedas,

It seems like you are working with two tables that not contain unique field values.
In my opinion, I'd like to suggest creating a calculated table with dax formulas to extract values from two tables and remove duplicate rows.

Bridge =
DISTINCT (
    UNION ( ALL ( ProjectBaselines[Project Id] ), ALL ( Projects[Project Id] ) )
)

After these steps, you can use this 'bridge' table to link two tables and use bridge table key and raw table fields with 'aggregate' function/methods(e.g. max, min...) to get specific values.

How to join many to many with a bridge table in POWER BI 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors