Selecting one value from column of another table with multiple values (Based on hierarchy criteria

[Anonymous]

I am trying to pull one value into a new table based on a column in another table with multiple values. The key is, I want to pull one distinct value based on a criteria. The values in the 2nd table are as:

Name	Value
A	1
A	1
A	2
B	1
B	1
B	3
C	1
C	1
C	4
D	1
D	1
D	5
E	1
E	1
E	4

Desired result is below as:

Pull the distinct value but:

If the value 2 is present, then pull 2

If the value 5 is present, then pull 5

If the value 1 is present, then pull 1

Name	Value
A	2
B	1
C	1
D	5
E	1

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try

Values =
VAR CurrentName = Table1[Nsme]
VAR T1 =
    FILTER ( Table2, Table2[Nsme] = CurrentName )
VAR T2 =
    DISTINCT ( SELECTCOLUMNS ( T1, "@Value", [Value] ) )
RETURN
    SWITCH ( TRUE (), 2 IN T2, 2, 5 IN T2, 5, 1 IN T2, 1 )

[Anonymous]

Thank you so much. I will definately continue to use this ongoing.