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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
IanT
Frequent Visitor

Lookup value based on 2 values, 1 exact match , 1 nearest match.

Hi all,

Part time BI-er here, feel like I have to relearn the basics everytime I dip my toes in!  Hoping one of you can help.

 

I have 2 tables, I need to look up a nearest value based on two values from table 1, I need an exact match of 'Name' and the nearest value to 'Start' (either higher or lower) 

 

Table1

NameStart

ABC

9.1044
ABC9.0900
DEF9.146
DEF9.1414

 

Table 2

NameStartRiseValue
ABC9.0875 51.6
ABC9.1 51.7
ABC9.1125 51.3
ABC9.125 51.4
DEF9.1375 51.95
DEF9.15 51.8
DEF9.1625 51.9

 

Is it best to add a column to table 1 or use a measure?

 

Either way, my desired result would be 

NameStartRiseValue result

ABC

9.1044 51.7
ABC9.0900 51.6
DEF9.146 51.8
DEF9.1414 51.95

 

Any suggestions?

1 ACCEPTED SOLUTION
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @IanT ,

1. Create a concatenated column in both the tables as shown below

Concat = CONCATENATE(Table1[Name], ROUND(Table1[Start],2))
Concat = CONCATENATE(Table2[Name], ROUND(Table2[Start],2))
 
2. Join the tables using the Concat column
Thejeswar_0-1686739868798.png

 

3. Pull Name and Start Column from Table1 and RiseValue from Table2
4. The Output will look like given below
Thejeswar_0-1686739575488.png

 

5. Restrict the Blanks using Visual Filter in Name. The Output should look as below

Thejeswar_1-1686739636668.png

 

Regards,

View solution in original post

2 REPLIES 2
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @IanT ,

1. Create a concatenated column in both the tables as shown below

Concat = CONCATENATE(Table1[Name], ROUND(Table1[Start],2))
Concat = CONCATENATE(Table2[Name], ROUND(Table2[Start],2))
 
2. Join the tables using the Concat column
Thejeswar_0-1686739868798.png

 

3. Pull Name and Start Column from Table1 and RiseValue from Table2
4. The Output will look like given below
Thejeswar_0-1686739575488.png

 

5. Restrict the Blanks using Visual Filter in Name. The Output should look as below

Thejeswar_1-1686739636668.png

 

Regards,

Thanks @Thejeswar ,  that is so simple and was not the direction my mind was heading in!!

 ‌‌

I've adapted it slightly as my table 2 goes up in increments of 0.0125 so I've added a column to table 1 using 

Concat = CONCATENATE(Table1[Name], MROUND(Table1[Start],0.0125))

For Table 2 I just merged the columns in Power Query. 

 

Many thanks.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.