Calculated Column in One table based on the other table in Power BI

[Anonymous]

I have below two tables "Project Details" and "Issues" linked with a relationship field Project ID.

You can see the issues are available for Project IDs 1, 2 and 3.

Now I need to create a calculated column in Project Details table called as "Indicator" which gives me three colors (Red, Yellow and "Green"). This calculated column will be based on the field "Issue Priority" in the Issues table.

Priority - 5 = Red

Priority - 3, 4 = Yellow

Priority - 1, 2 or No Priority value available = Green

I need the below Indicator calculated field.

Project 1 is having the priorities 2 and 3. We need to take the maximum priorities available whcih is 3 and so it will be Yellow as per the rule.

Project 2 is having the priorities 4 and 5. We need to take the maximum prioritiues available which is 5 and so it will be Red as per the rule.

Project 3 is having the priority 1 and so it will be Green as per the rule.

Project 4 and 5 doesn't have any issues and so it will be Green as per the rule.

Kinldy help with DAX query.

[amitchandak]

@Anonymous , new column in Project Details

new column =
var _max = maxx(relatedtable(issues), isssues[Issue Priority])
return
switch(true(),
_max = 5, "Red",
_max in {3,4}, "Yellow",
_max in {1,2}, "Green"
)

Also share 4 ways to move data from 1 table to another

https://www.youtube.com/watch?v=Wu1mWxR23jU

https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

@amitchandak  This formula doesn not work for Project ID 4 and 5 since those projects are not available in the Issues table. 

If the projects are not available in issues table, then the respective projects should have indicators as Green color.

Please help.

[amitchandak · Accepted solution]

@Anonymous , Try

new column =
var _max = maxx(relatedtable(issues), isssues[Issue Priority])
return
switch(true(),

isblank(_max), "Green",
_max = 5, "Red",
_max in {3,4}, "Yellow",
_max in {1,2}, "Green"
)

or

new column =
var _max = maxx(relatedtable(issues), isssues[Issue Priority])+0
return
switch(true(),

_max = 0, "Green",
_max = 5, "Red",
_max in {3,4}, "Yellow",
_max in {1,2}, "Green"
)