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 spend per supplier

Table 2 = A dimension table stating that If I spend X amount (or above) it should be categorized as "Strategic" for instance. 

I guess what I need is nested IF Statements, Switch wont do it. But I just cant get to that final part. 

I do not want it to be exact numbers, as suppliers spend from 0 - 1000000, and everything in between. So rather a range. 



 

I tried something in the lines of:


IF( [table1]spend >= [table2] spend, THEN RETURN Category. 

Example: 

IF Apple spend is 500000, it is considered a "Common" Supplier as it falls between the Range of "Common" and "Strategic.

 

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

2 Replies

  • 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!