IF + AND Code

[ArchStanton]

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?

[Anonymous · Accepted solution]

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.

[ArchStanton]

Thank you, this works perfectly!

[Anonymous]

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 ☕

[ArchStanton]

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