Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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:

 

 

 

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!

 

 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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