IF DAX statement between two values

Question

Hello Everyone,

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

Would appreciate your assistance.

(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)

Thanking you in advance.

Answer 1

You need to add a "bucket" table to your data model.

Answer 2

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")

Answer 3

Why not use the SWITCH syntax?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 4

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

Answer 5

Thank you...will give this a try.