To Pull the value from a table with no relationship/ column in common

[Anonymous]

Hey there, seems like I need your help once more.

First, what you need to know; there is a table describing phases (A to D), and their respective thresholds (most of them start at 0.8 or 80% to enter "Yellow State"). 

 

 There is a measure calculated, example for Start B phase (Project Name context; Each project has its own Start B phase: the measure displays whether its above or below a given threshold)

This conditional formatting thing is based on "Format" tab. But, it has to be formatted based on the table abovementioned. 
Here's what I did:

VAR phase_b_condition = 
   How to Pull a corresponding value so it knows it is Phase B?

VAR phase_b_flags = 
    SWITCH ( [phase b Readiness],
        [phase b Readiness] < phase_b_condition, "FlagLow",
        [phase b Readiness] > phase_b_condition, "FlagHigh",
        "FlagMedium"
    )

Question as is in the code chunk:
- How to retrieve a corresponding threshold value for condition from a table that has no relationships with anything else, so I can compare it with the measure result.
I just don't have enough experience to deal with it. 

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample.

Table 1:

Table 2:

The two tables don't have relationship, now in my understanding, you want to get which range of Table 2 is in PhaseB according to Table 1, here's my solution, create a measure:

Measure =
VAR MAX_phase_b_condition =
    MAXX (
        FILTER ( 'Table 1', 'Table 1'[Phase] = "Start B" ),
        'Table 1'[Max_Threshold]
    )
VAR MIN_phase_b_condition =
    MAXX (
        FILTER ( 'Table 1', 'Table 1'[Phase] = "Start B" ),
        'Table 1'[Min_Threshold]
    )
RETURN
    SWITCH (
        TRUE,
        MAX ( 'Table 2'[phase b Readiness] ) < MIN_phase_b_condition, "FlagLow",
        MAX ( 'Table 2'[phase b Readiness] ) > MAX_phase_b_condition, "FlagHigh",
        "FlagMedium"
    )

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Note: Table 2 is a measure. I will also check if it's gonna affect the overall result

[Anonymous]

Hey there! I am sorry for the long reply. I'll try it today and will let you know it if it'll work!