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

[IR15H]

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_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.

 

[Matt Allington]

You need to use a banding approach. I wrote an article about it here

Banding in DAX

Banding in DAX is a technique that allows you to “group” your data in a table into “bands” so as to report data in a useful way to comprehend.

exceleratorbi.com.au

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.

 

[IR15H]

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]))