Retrieve a value using multiple criteria and a range lookup in PowerBI

IR15H

New Member
Joined
Jun 8, 2013
Messages
13
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
DESIRED_VALUEVARIABLE_1VARIABLE_2START_OF_RANGEEND_OF_RANGE
ATEXT_1100050000
BTEXT_220005001500


Table_2
VARIABLE_1VARIABLE_2NUMBERDESIRED_VALUE
TEXT_11005??? (in this example "A")
TEXT_22000100??? (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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You need to use a banding approach. I wrote an article about it here
You need to write a new calculated column in Table 2 by lifting the data in table 2 and placing these data as a filter on table 1, then extracting the result back into the new calculated column. It’s all covered in the article.
 
Upvote 0
Solution
Matt,

Thanks for your help / article.

For the benefit of other potentially looking to do something similar in future, I think I've got it working by creating the below calculated column in DAX;

Column = CALCULATE(
VALUES(Table.1[DESIRED_VALUE]),
FILTER(Table.1,
Table.2[VARIABLE_1] = Table.1[VARIABLE_1] &&
Table.2[VARIABLE_2] = Table.1[VARIABLE_2] &&
Table.2[NUMBER] > Table.1[START_OF_RANGE] &&
Table.2[NUMBER] <= Table.1[END_OF_RANGE]))
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top