Lookup Value corresponding to matching ID from another table based on a condition in Power Bi

[Raj12]

Create calculated column (in table2)with Lookup Value corresponding to matching ID from another table(Table 1) based on a condition where output value2 ,is lookup value with highest priroity from Table1 i.e.

1. Pause

2. No Pause

3. Other

Table 1
Id1	Value1
2002-10/01/2023	No Pause
2002-10/01/2023	Pause
2002-10/01/2023	Other
2103-10/01/2023	Pause
2103-10/01/2023	Pause
2105-10/01/2023	Other
2105-11/01/2023	No Pause
2106-11/01/2023	Other

Table 2
Id2	Value2
2002-10/01/2023	Pause
2103-10/01/2023	Pause
2105-10/01/2023	No Pause
2106-11/01/2023	Other

I treid creating calculated column as:
Value2 = LOOKUPVALUE(Table1[Value1],Table1[Merged Copy],Table2[ID2])
This gives error as mutiple value was supplied

Another way gives only first value 
Value2 =
var xval= CALCULATE(FIRSTNONBLANK(Table1[Value1],TRUE()), FILTER(Table1,Table1[ID1]=Table2[ID2] ) )
return
if(xval="Pause" ,"Pause",IF(xval="No Pause","No Pause","Other"))

[v-kongfanf-msft]

Hi @Raj12 ,

The formula @Greg_Deckler  provided is worth looking at.

Also I created the following formula and was able to get the same results.

Value2 = 
VAR CurrentID = Table2[Id2]
VAR HighestPriorityValue =
    SELECTCOLUMNS (
        FILTER (
            Table1,
            Table1[Id1] = CurrentID
                && ( Table1[Value1] = "Pause"
                || Table1[Value1] = "No Pause" )
        ),
        "Value1", Table1[Value1]
    )
RETURN
    IF (
        COUNTROWS ( HighestPriorityValue ) > 0,
        MAXX ( HighestPriorityValue, [Value1] ),
        "Other"
    )

Best Regards,
Adamk Kong

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

[Greg_Deckler · Accepted solution]

@Raj12 Maybe:

Column = 
  VAR __ID = [Id2]
  VAR __Values = SELECTCOLUMNS( FILTER('Table 1', [Id1] = __ID), "__Value1", [Value1] )
  VAR __Result =
    SWITCH(TRUE(),
      "Pause" IN __Values, "Pause",
      "No Pause" IN __Values, "No Pause",
      "Other" IN __Values, "Other",
      BLANK()
    )
RETURN
  __Result