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.
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?
Solved! Go to Solution.
Hi @IanT ,
1. Create a concatenated column in both the tables as shown below
5. Restrict the Blanks using Visual Filter in Name. The Output should look as below
Regards,
Hi @IanT ,
1. Create a concatenated column in both the tables as shown below
5. Restrict the Blanks using Visual Filter in Name. The Output should look as below
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |