Forum Discussion
IF + AND Code
Hi,
I have the following code in a Calculated Column that I can't get to work. I would like to exclude anything created before 1st Apr 2021 and Resolved before applying the IF statement.
Days to Validation Bins =
IF(AND('Cases'[Created On] < "01/04/2021", 'Cases'[statecode] = "Resolved"),BLANK(),
IF(ISBLANK('Cases'[Days to Validation]),BLANK(),
IF( 'Cases'[Days to Validation] < 30 , " 0-1 Mth",
IF('Cases'[Days to Validation] < 60, " 1-2 Mths",
IF('Cases'[Days to Validation] < 90, " 2-3 Mths",
"Older")))))The error message I get is:
Any ideas how to fix this?
Hi ArchStanton ,
You can try the following dax.
Complexity Bins = SWITCH ( TRUE (), ISBLANK ( [Validation to Closure] ), BLANK (), [Validation to Closure] < 91.3, REPT ( UNICHAR ( 8203 ), 1 ) & "" & " 0-3 Mths", [Validation to Closure] < 182.5, REPT ( UNICHAR ( 8203 ), 2 ) & "" & " 3-6 Mths", [Validation to Closure] < 273.5, REPT ( UNICHAR ( 8203 ), 3 ) & "" & " 6-9 Mths", [Validation to Closure] < 365.25, REPT ( UNICHAR ( 8203 ), 4 ) & "" & " 9-12 Mths", [Validation to Closure] < 456.25, REPT ( UNICHAR ( 8203 ), 5 ) & "" & " 12-15 Mths", [Validation to Closure] < 547.5, REPT ( UNICHAR ( 8203 ), 6 ) & "" & " 15-18 Mths", [Validation to Closure] < 638.75, REPT ( UNICHAR ( 8203 ), 7 ) & "" & " 18-21 Mths", [Validation to Closure] < 730.5, REPT ( UNICHAR ( 8203 ), 8 ) & "" & " 21-24 Mths", [Validation to Closure] < 821.5, REPT ( UNICHAR ( 8203 ), 9 ) & "" & " 24-27 Mths", [Validation to Closure] < 912.5, REPT ( UNICHAR ( 8203 ), 10 ) & "" & " 27-30 Mths", [Validation to Closure] < 1004.5, REPT ( UNICHAR ( 8203 ), 11 ) & "" & " 30-33 Mths", [Validation to Closure] < 1095.5, REPT ( UNICHAR ( 8203 ), 12 ) & "" & " 33-36 Mths", REPT ( UNICHAR ( 8203 ), 13 ) & " 36+ Mths" )Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi ArchStanton ,
You can try the following dax.
Complexity Bins = SWITCH ( TRUE (), ISBLANK ( [Validation to Closure] ), BLANK (), [Validation to Closure] < 91.3, REPT ( UNICHAR ( 8203 ), 1 ) & "" & " 0-3 Mths", [Validation to Closure] < 182.5, REPT ( UNICHAR ( 8203 ), 2 ) & "" & " 3-6 Mths", [Validation to Closure] < 273.5, REPT ( UNICHAR ( 8203 ), 3 ) & "" & " 6-9 Mths", [Validation to Closure] < 365.25, REPT ( UNICHAR ( 8203 ), 4 ) & "" & " 9-12 Mths", [Validation to Closure] < 456.25, REPT ( UNICHAR ( 8203 ), 5 ) & "" & " 12-15 Mths", [Validation to Closure] < 547.5, REPT ( UNICHAR ( 8203 ), 6 ) & "" & " 15-18 Mths", [Validation to Closure] < 638.75, REPT ( UNICHAR ( 8203 ), 7 ) & "" & " 18-21 Mths", [Validation to Closure] < 730.5, REPT ( UNICHAR ( 8203 ), 8 ) & "" & " 21-24 Mths", [Validation to Closure] < 821.5, REPT ( UNICHAR ( 8203 ), 9 ) & "" & " 24-27 Mths", [Validation to Closure] < 912.5, REPT ( UNICHAR ( 8203 ), 10 ) & "" & " 27-30 Mths", [Validation to Closure] < 1004.5, REPT ( UNICHAR ( 8203 ), 11 ) & "" & " 30-33 Mths", [Validation to Closure] < 1095.5, REPT ( UNICHAR ( 8203 ), 12 ) & "" & " 33-36 Mths", REPT ( UNICHAR ( 8203 ), 13 ) & " 36+ Mths" )Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, this works perfectly!
Hello ArchStanton, it seems there is a type error. In the first line, you are comparing a date type with a string type. Try using:
DATEVALUE("01/04/2021")https://learn.microsoft.com/en-us/dax/datevalue-function-dax
Additionally, using a SWITCH statement would make it much more readable. You can learn about the SWITCH function in DAX
https://learn.microsoft.com/en-us/dax/switch-function-dax
hope it helped ☕Thanks for your help, DATEVALUE worked perfectly!
I've been wondering how to incorporate SWITCH into some of my DAX but I dont think it will always be possible e.g, how would I use it here:
Days to Validation Bins = IF(AND('Cases'[Created On] < DATEVALUE("01/04/2021"), 'Cases'[statecode] = "Resolved" || "2"),BLANK(), IF(ISBLANK('Cases'[Days to Validation]),BLANK(), IF( 'Cases'[Days to Validation] < 30 , " 0-1 Mth", IF('Cases'[Days to Validation] < 60, " 1-2 Mths", IF('Cases'[Days to Validation] < 90, " 2-3 Mths", "Older")))))Or here: where I have 'sorted' them 1 to 13 for Visual formatting purposes?
Thanks
Complexity Bins = IF(ISBLANK('Cases'[Validation to Closure]),BLANK(), if( 'Cases'[Validation to Closure] < 91.3, REPT(UNICHAR(8203),1)&" 0-3 Mths", if('Cases'[Validation to Closure] < 182.5, REPT(UNICHAR(8203),2)&" 3-6 Mths", if('Cases'[Validation to Closure] < 273.5, REPT(UNICHAR(8203),3)&" 6-9 Mths", if('Cases'[Validation to Closure] < 365.25, REPT(UNICHAR(8203),4)&" 9-12 Mths", if('Cases'[Validation to Closure] < 456.25, REPT(UNICHAR(8203),5)&" 12-15 Mths", if('Cases'[Validation to Closure] < 547.5, REPT(UNICHAR(8203),6)&" 15-18 Mths", if('Cases'[Validation to Closure] < 638.75, REPT(UNICHAR(8203),7)&" 18-21 Mths", if('Cases'[Validation to Closure] < 730.5, REPT(UNICHAR(8203),8)&" 21-24 Mths", if('Cases'[Validation to Closure] < 821.5, REPT(UNICHAR(8203),9)&" 24-27 Mths", if('Cases'[Validation to Closure] < 912.5, REPT(UNICHAR(8203),10)&" 27-30 Mths", if('Cases'[Validation to Closure] < 1004.5, REPT(UNICHAR(8203),11)&" 30-33 Mths", if('Cases'[Validation to Closure] < 1095.5, REPT(UNICHAR(8203),12)&" 33-36 Mths", REPT(UNICHAR(8203),13)&" 36+ Mths")))))))))))))
