Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to return a specific field for a record

Hi, I have connected PowerBI to Microsoft Project Online and have a question on how to return the relevant project and programme manager for a project. All projects, programmes and portfolios are co...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    hi  Anonymous 

    Create two column as below:

    PROGRAMME MGR = CALCULATE(MAX('Table'[Manager]),FILTER('Table','Table'[Portfolio]=EARLIER('Table'[Portfolio])&&'Table'[Programme]=EARLIER('Table'[Programme])&&'Table'[Type]="Programme"))
    PORTFOLIO MGR = CALCULATE(MAX('Table'[Manager]),FILTER('Table','Table'[Portfolio]=EARLIER('Table'[Portfolio])&&'Table'[Type]="Portfolio"))

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

manikumar34's avatar
manikumar34
Icon for Solution Sage rankSolution Sage
5 years ago

Anonymous , 

 

You can create a conditional column on Power query to do so. 

#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [Type] = "Portfolio" then [Manager] else null),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Portfoli MGR"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Type] = "Programme" then [Manager] else null),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column1",{{"Custom", "Programme MGR"}})

 

 

If you want to fill the oother rows which are empty, on else part you can mention the Value to be filled. 

 

Regards, 

Manikumar