IF Statements with Range conditions

[tonijj]

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.

[quentin_vigne · Accepted solution]

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]

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!