# IF DAX statement between two values

### Question

• Hello Everyone,

I have a DAX formula which Is not giving me the results required.

(If a value is between the below different criteria values...show it otherwise return a blank).

I'm sure this can be incorporated into 1 DAX formula entirely.

Name of table= [QUERY]
Column data extraction = [VOLUMES]

My formulas in 3 columns which I will call later with a slicer:
Column name:MV
= IF(AND([VOLUMES]>0,[VOLUMES]<199),[VOLUMES],0)

Column name:PT
= IF(AND([VOLUMES]>200,[VOLUMES]<1499),[VOLUMES],0)

Column name:VP
= IF(AND([VOLUMES]>1500,[VOLUMES]<1501),[VOLUMES],0)

Wednesday, July 25, 2012 12:26 PM

• I'd create a table "MyBuckets" like this

 LowVolume HighVolume Value 0 199 MV 200 1499 PT 1500 1501 VP

Then you can use DAX as below:

=calculate(values(MyBuckets[Value], filter(MyBuckets, query[volumes])>=MyBuckets[LowVolume] && query[volumes]<MyBuckets[HighVolume])

-- This posting is provided "AS IS" with no warranties, and confers no rights

• Marked as answer by Tuesday, July 31, 2012 12:44 PM
Tuesday, July 31, 2012 12:17 AM

### All replies

• You need to add a "bucket" table to your data model.
Wednesday, July 25, 2012 6:57 PM
• Hello David, Would you provide me with a example on how to predefined this data table? This is the first time I'm face with this issue.

I was thinking there was something wrong with my formula.

Would this formula work after the bucket is linked?

=IF(AND([VOLUMES]>0,[VOLUMES]<199),"MV")&IF(AND([VOLUMES]>200,[VOLUMES]<1499),"PT")&IF(AND([VOLUMES]>1500,[VOLUMES]<1501),"VP")

Wednesday, July 25, 2012 7:08 PM
• Why not use the SWITCH syntax?

Cheers, Raunak | t: @raunakjhawar | My Blog

Thursday, July 26, 2012 8:43 AM
• I'd create a table "MyBuckets" like this

 LowVolume HighVolume Value 0 199 MV 200 1499 PT 1500 1501 VP

Then you can use DAX as below:

=calculate(values(MyBuckets[Value], filter(MyBuckets, query[volumes])>=MyBuckets[LowVolume] && query[volumes]<MyBuckets[HighVolume])

-- This posting is provided "AS IS" with no warranties, and confers no rights

• Marked as answer by Tuesday, July 31, 2012 12:44 PM
Tuesday, July 31, 2012 12:17 AM
• Thank you...will give this a try.
Tuesday, July 31, 2012 12:44 PM