Forum Discussion
Power ParticipantIF + AND Code
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.
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 ☕
ArchStantonThanks 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")))))))))))))