Forum Discussion

KaZeK's avatar
KaZeK
Frequent Visitor
3 years ago
Solved

Assigning range to value from not connected, different table

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's avatar
    v-yadongf-msft
    3 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • KaZeK's avatar
      KaZeK
      Frequent Visitor
      3 years ago

      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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      KaZeK
      Frequent Visitor
      3 years ago

      Dear v-yadongf-msft

       

      Thank you so much. It works perfectly!!!

       

      Regards

      KaZeK