Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 cre...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    5 years ago

    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"
    )

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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"
)