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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors