Return the highest value from another table for each row

[Anonymous]

I have a table of projects (one row per project), then a table for risks and another for issues associated to each project (there can be any number of rows per project in these tables). I'd like to add two columns to the projects table that returns a number of 1-3 forming an overall risk and issue rating for the project, based on the ratings specified in the risks and issues tables. 

Each risk and issue added to a project is given a rating of 1-3: 1=Green, 2=Yellow, 3=Red. If a red risk or issue exists on a project, I want the overall project risk/issue rating to be 3. Then if a yellow risk or issue exists and a red one doesn't, the overall rating should be 2. If no yellow or red ones exist, it should be 1. 

Is it possible to perform a merge between these tables but have only the highest number returned? Any other ideas on the simplest way to achieve this would be appreciated. 

[v-huizhn-msft]

Hi @Anonymous,

If there is relationship between the three tables? Could you please create three fake sample table table for further analysis? So that we can post solution which is close to your requirement. Thanks for understanding.

Best Regards,
Angelia

[Anonymous]

Hi Angelia

Yes there is a relationship between the 3 tables via the ProjectId. These are the standard tables for Project Online available via the Project Reporting OData feed (I've just renamed a lot of the columns). Attached is a screenshot showing the relationship between the tables and sample of the risks/issues tables that shows the Priority value; which is a Whole Number (1-3). I was hoping to perform a type of merge that picks up the first Priority value for each Project Id; similar to how a VLOOKUP would work in Excel where it returns the first match it finds (if I sorted the Risks/Issues tables descending by Priority, it would pick up the highest one for each project). 

Thanks

Olivia

[Ashish_Mathur]

Hi @Anonymous,

Share the link from where i can download your file and also show the expected result.

[Anonymous]

Hi

The file contains confidential information. Here are screenshots of sample data with a formula I would use in Excel to achieve this. Is it possible to achieve something like this in Power BI? The yellow column shows the result I'm after.

[Ashish_Mathur]

Hi,

Yes.  This should be possible.  Dummy your dataset and share the download link.

[Anonymous]

Please advise where I can upload the file then?

[Ashish_Mathur]

Try Google Drive.

[Anonymous]

I can't access Google drive on my PC and cannot work out how to share a file with just anyone in One Drive. 

I'm also totally stuck on how to dummy the data in my existing file, so all I've done is copied the sample data from my Excel workbook into a blank Power BI file. In which case I've pasted the tables from the workbook below, you should be able to copy these into a blank PBI file, right? I've specified the data types in brackets for each column. I'm happy to then work out how to implement the solution into my real file. 

Apologies I can't achieve what you're asking and would appreciate any help that can be provided.

ProjectId (text)	Project Name (text)
1a2345cd-6e7f-g8901-12h3-45678i901234	Project A
1a2345cd-6e7f-g8901-12h3-45678i901238	Project B
1a2345cd-6e7f-g8901-12h3-45678i901236	Project C
1a2345cd-6e7f-g8901-12h3-45678i901237	Project D
1a2345cd-6e7f-g8901-12h3-45678i901235	Project E

ProjectId (text)	Project Name (text)	Title (text)	Rating (Whole Number)
1a2345cd-6e7f-g8901-12h3-45678i901234	Project A	Risk 4	3
1a2345cd-6e7f-g8901-12h3-45678i901236	Project C	Risk 3	3
1a2345cd-6e7f-g8901-12h3-45678i901235	Project E	Risk 7	3
1a2345cd-6e7f-g8901-12h3-45678i901236	Project C	Risk 8	2
1a2345cd-6e7f-g8901-12h3-45678i901237	Project D	Risk 5	2
1a2345cd-6e7f-g8901-12h3-45678i901235	Project E	Risk 2	2
1a2345cd-6e7f-g8901-12h3-45678i901234	Project A	Risk 1	1
1a2345cd-6e7f-g8901-12h3-45678i901238	Project B	Risk 11	1
1a2345cd-6e7f-g8901-12h3-45678i901237	Project D	Risk 9	1
1a2345cd-6e7f-g8901-12h3-45678i901237	Project D	Risk 10	1
1a2345cd-6e7f-g8901-12h3-45678i901235	Project E	Risk 6	1

[Ashish_Mathur · Accepted solution]

Hi @Anonymous,

You may refer to my solution here.

Hope this helps.

[Anonymous]

This is exactly what I needed, thank you!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Yes.  This should be possible.  Dummy your dataset and share the download link.