cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

 Name Start ABC 9.1044 ABC 9.0900 DEF 9.146 DEF 9.1414

Table 2

 Name Start RiseValue ABC 9.0875 51.6 ABC 9.1 51.7 ABC 9.1125 51.3 ABC 9.125 51.4 DEF 9.1375 51.95 DEF 9.15 51.8 DEF 9.1625 51.9

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

Either way, my desired result would be

 Name Start RiseValue result ABC 9.1044 51.7 ABC 9.0900 51.6 DEF 9.146 51.8 DEF 9.1414 51.95

Any suggestions?

1 ACCEPTED SOLUTION
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

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

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

Regards,

2 REPLIES 2
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

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

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

Regards,

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.