Hi,
I am after some assistance from anyone who's able to please as I've been searching for hours and still can't find a tutorial/solution I can successfully modify for what frustratingly feels like it must have a relatively simple solution.
In essence, I have two tables in PowerBI that I would like to merge/lookup in a performance-efficient manner. The current methodology I've used (due to lack of knowledge) expands one table to remove the range and instead have millions of unique rows, which then unsurprisingly becomes very slow.
An example below of my two tables is as below.
Table_1
Table_2
Essentially, the task I would like to complete is....
Get the relevant Table_1 DESIRED_VALUE and enter it into Table_2 where the following conditions are true;
Table_1.VARIABLE_1 = Table_2.VARIABLE_1 and Table_1.VARIABLE_2 = Table_2.VARIABLE_2 and Table_1.START_OF_RANGE <= Table_2.NUMBER and Table_1.END_OF_RANGE >= Table_2.NUMBER
Due the the nature of the data I have, there will only ever be one DESIRED_VALUE result for the above conditions and it will always 'find' a result.
Thanks in advance.
I am after some assistance from anyone who's able to please as I've been searching for hours and still can't find a tutorial/solution I can successfully modify for what frustratingly feels like it must have a relatively simple solution.
In essence, I have two tables in PowerBI that I would like to merge/lookup in a performance-efficient manner. The current methodology I've used (due to lack of knowledge) expands one table to remove the range and instead have millions of unique rows, which then unsurprisingly becomes very slow.
An example below of my two tables is as below.
Table_1
DESIRED_VALUE | VARIABLE_1 | VARIABLE_2 | START_OF_RANGE | END_OF_RANGE |
A | TEXT_1 | 100 | 0 | 50000 |
B | TEXT_2 | 2000 | 500 | 1500 |
Table_2
VARIABLE_1 | VARIABLE_2 | NUMBER | DESIRED_VALUE |
TEXT_1 | 100 | 5 | ??? (in this example "A") |
TEXT_2 | 2000 | 100 | ??? (in this example "B") |
Essentially, the task I would like to complete is....
Get the relevant Table_1 DESIRED_VALUE and enter it into Table_2 where the following conditions are true;
Table_1.VARIABLE_1 = Table_2.VARIABLE_1 and Table_1.VARIABLE_2 = Table_2.VARIABLE_2 and Table_1.START_OF_RANGE <= Table_2.NUMBER and Table_1.END_OF_RANGE >= Table_2.NUMBER
Due the the nature of the data I have, there will only ever be one DESIRED_VALUE result for the above conditions and it will always 'find' a result.
Thanks in advance.