Filter/Row context between related tables

Question

Hello, I have a question regarding filter context between related tables.
I have two tables, one containing part numbers and some other info about the parts and another table with just the part numbers and a COLOR column containing the priority of the part. Possible values: Low, Medium and High.
The relationship is the part number.

I'm trying to write a measure that I can use as a conditional background showing red if COLOR = High, yellow if COLOR = Medium and green if COLOR = Low.
What I did initially is this:

If I then for example create a table with part numbers from Brakes&Wheels_ table and apply this measure every row will turn yellow (assuming there exists a part with every priority type possible) because alphabetically Medium comes last when MAX() is applied. If I add the COLOR column to the table then the correct filtering happens i.e. the table looks at evert part separately and applies the correct color to the background. I want to implement that behaviour but without needing to add a column from the related table into my visualization.
Does anybody have a suggestion on how that can be done? I have tried putting the MAX() inside a CALCULATE() and using USERELATIONSHIP() or RELATEDTABLE() inside the CALCULATE()

anonymous · Accepted Answer

Hi&nbsp;MateuszO&nbsp;,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a measure
PartColorPriority = 
VAR CurrentPartColor = 
    CALCULATE(
        MAX(Brakes_AND_Wheels[Priority]), 
        RELATEDTABLE(Brakes_AND_Wheels)
    )
RETURN
    SWITCH(
        TRUE(),
        CurrentPartColor = "High", "#ff0000",
        CurrentPartColor = "Medium", "#ffed5f",
        CurrentPartColor = "Low", "#66ff33"
    )
Apply measure to cell elements background

Final output

&nbsp;
Best regards,
Albert He
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly