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
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
Super User
Super User

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
Super User
Super User

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
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!

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!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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