Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
raragan
Frequent Visitor

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

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:

NameValue
A1
A1
A2
B1
B1
B3
C1
C1
C4
D1
D1
D5
E1
E1
E4

 

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

 

NameValue
A2
B1
C1
D5
E1

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @raragan 

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 )

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @raragan 

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 )

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.