Forum Discussion

tonijj's avatar
tonijj
Icon for Helper IV rankHelper IV
4 years ago
Solved

IF Statements with Range conditions

Hi,  I have a well, something I thought was a simple problem to solve, but I just cant get my arms around it.    I have two tables:   Table 1 = Spend data - Simple data set showing how much I ...
Need Help
  • quentin_vigne's avatar
    quentin_vigne
    4 years ago

    Hi tonijj,

     

    Just create a new column with this formula : 

     

     
Category = 
var JoinCol = 'Table'[Spend] 
var NewCol = CALCULATE(MAX('Table (2)'[Spend]),'Table (2)'[Spend]<=JoinCol) 
var CatNewCol = LOOKUPVALUE('Table (2)'[Category], 'Table (2)'[Spend],NewCol) 
return CatNewCol

     

    Table is Table 1 and Table 2 is Table 2.

    You need to create this new column in Table 1.

     

    Hope it works

    - Quentin

quentin_vigne's avatar
quentin_vigne
Icon for Solution Sage rankSolution Sage
4 years ago

Hi tonijj,

 

Just create a new column with this formula : 

 

 
Category = 
var JoinCol = 'Table'[Spend] 
var NewCol = CALCULATE(MAX('Table (2)'[Spend]),'Table (2)'[Spend]<=JoinCol) 
var CatNewCol = LOOKUPVALUE('Table (2)'[Category], 'Table (2)'[Spend],NewCol) 
return CatNewCol

 

Table is Table 1 and Table 2 is Table 2.

You need to create this new column in Table 1.

 

Hope it works

- Quentin

  • tonijj's avatar
    tonijj
    Icon for Helper IV rankHelper IV
    4 years ago

    Hi quentin_vigne 

    Thanks! 

    I would say that YES it works! Had some minor issues, but that had to do with my data and not your solution. 

    Highly appreciated!

    Now, I need to take a few minutes to really understand the concept behind the formula so I can use it in the future, big thanks!