Assigning range to value from not connected, different table

[KaZeK]

Dear Community, 

I have a doubt and I do not have any idea how to handle it. Basically I have two tables, one of them contains thousends of raws with unique name and specific value:

name1      value1

...               ...

...               ...

nameX       valueX

In the second table I am calculating Ranges, the result of this calculation is the table like below:

How to assign range from second table to the value from table number one, and add column in table one where I will provide correct Range Name? I Unfortunately I cannot use LOOKUPVALUE() function due to the fact that tables are not connected... I know SWITCH() function, but I do not know how to use values from not connected table...

Finally, I need to get table like below:

name1      value1    RangeName1 (column name from 2nd table: SR30PeriodNameX)

...               ...

...               ...

nameX       valueX  RangeNameX

Please, help me... It cannot be so difficult...

Regards

Kazek

[v-yadongf-msft · Accepted solution]

Hi @KaZeK ,

Please try following DAX:

RangeName = MAXX(
    FILTER(
        'SR30_periods_adj',
        'SR30_periods_adj'[SR30PeriodMinValue] <= EARLIER('StockList'[SR30]) &&
        'SR30_periods_adj'[SR30PeriodMaxValue] >= EARLIER('StockList'[SR30])),
        'SR30_periods_adj'[SR30PeriodName]
)

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KaZeK]

Dear v-yadongf-msft

Thank you so much. It works perfectly!!!

Regards

KaZeK

[KaZeK]

Hello, 

I am sharing file with data. The main question is how to define range for SR30 value located in StockList table, where ranges are calculated and provided in second table - SR30_periods_adj (tables are not connected), and finally how to copy range name - SR30PeriodName - from second table - SR30_periods_adj to table StockList.

https://drive.google.com/file/d/1H9x2V7dxnPRqr4FtY2ojMAqCu_7ks4cw/view?usp=sharing 

Regards

KaZeK

[Anonymous]

Hello what do you mean by range? Like maxvalue - min value? And can you provide a sample to download or use?

[KaZeK]

Hello Eliasayy,

I mean that Value from table number one belongs to the range from table number two. It means, my Value is bigger then lower range (SR30PeriodMinValue) and lower then higher range (SR30PeriodMaxValue). When Value and range is defined I would like to take range name (SR30PeriodName) and copy this name to table number one.

regards

Kazek