MS Project baseline tracking

[Anonymous]

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:

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:

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!

[v-shex-msft · Accepted solution]

HI @Anonymous,

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