cancel
Showing results for
Did you mean:

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

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

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors