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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.