Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 years ago
Solved

Help with evaluating some DAX code

Hi,

 

I'm struggling to work out how this piece of code is being being evaluated, in particular what is being calculated first, second and so on. If some could break it down to show what each step is in order it would be a great help.

 

Also, Is there a way of making this code simpler using Variables?

Days ERT to Outcome = 
IF (
    NOT ( ISBLANK ( 'Cases'[ERT Start FlagDate] )),
    IF (
        ISBLANK ( 'Cases'[formofauthorityreceiveddate] ),
        DATEDIFF (
            IF (
                'Cases'[Created Date] > 'Cases'[ERT Start FlagDate],
                'Cases'[Created on],
                'Cases'[ERT Start FlagDate]
            ),
            IF ( ISBLANK ( 'Cases'[ERT End FlagDate] ), NOW (), 'Cases'[ERT End FlagDate] ),
            DAY
        ),
        DATEDIFF ( 'Cases'[ERT Start FlagDate], 'Cases'[ERT End FlagDate], DAY )
    ),
    BLANK ()
)

Thanks,

 

 

Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    IF statements work from the top in so it check 1, the 2 etc:

    It startts with 1 and IF 'Cases[ERT Start Flag Date] is not blank it goes to section A which starts the 2 check, if it is blank it gos to B and returns a BLANK().

     

  • mlsx4's avatar
    mlsx4
    3 years ago

    Hi ArchStanton 

     

    The explanation from jdbuchanan71 is perfect. Regarding your second question, you can use variables in this way:

     

    Days ERT to Outcome = 

var _startFlag= ISBLANK ( 'Cases'[ERT Start FlagDate] )
var _endFlag= ISBLANK ( 'Cases'[ERT End FlagDate] )

IF (
    NOT ( _startFlag),
    IF (
        ISBLANK ( 'Cases'[formofauthorityreceiveddate] ),
        DATEDIFF (
            IF (
                'Cases'[Created Date] > 'Cases'[ERT Start FlagDate],
                'Cases'[Created on],
                'Cases'[ERT Start FlagDate]
            ),
            IF ( _endFlag , NOW (), 'Cases'[ERT End FlagDate] ),
            DAY
        ),
        DATEDIFF ( 'Cases'[ERT Start FlagDate], 'Cases'[ERT End FlagDate], DAY )
    ),
    BLANK ()
)

     

    However, as you can see, it doesn't make much sense. Variables avoid repetitive calculus, but in this case, you don't have repetitive pieces

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    3 years ago

    IF statements work from the top in so it check 1, the 2 etc:

    It startts with 1 and IF 'Cases[ERT Start Flag Date] is not blank it goes to section A which starts the 2 check, if it is blank it gos to B and returns a BLANK().

     

    • mlsx4's avatar
      mlsx4
      Icon for Memorable Member rankMemorable Member
      3 years ago

      Hi ArchStanton 

       

      The explanation from jdbuchanan71 is perfect. Regarding your second question, you can use variables in this way:

       

      Days ERT to Outcome = 

var _startFlag= ISBLANK ( 'Cases'[ERT Start FlagDate] )
var _endFlag= ISBLANK ( 'Cases'[ERT End FlagDate] )

IF (
    NOT ( _startFlag),
    IF (
        ISBLANK ( 'Cases'[formofauthorityreceiveddate] ),
        DATEDIFF (
            IF (
                'Cases'[Created Date] > 'Cases'[ERT Start FlagDate],
                'Cases'[Created on],
                'Cases'[ERT Start FlagDate]
            ),
            IF ( _endFlag , NOW (), 'Cases'[ERT End FlagDate] ),
            DAY
        ),
        DATEDIFF ( 'Cases'[ERT Start FlagDate], 'Cases'[ERT End FlagDate], DAY )
    ),
    BLANK ()
)

       

      However, as you can see, it doesn't make much sense. Variables avoid repetitive calculus, but in this case, you don't have repetitive pieces