Why does removing blanks make my DAX faster?

[TheSaffar]

Hi there, hope i'm in the right forum.
I have written a DAX measure i call "Days in Period" that used to have a refresh time of around 2500ms, which i have somehow managed to cut down to around 200ms, but i don't quite understand why my "solution" worked.

I use the measure in a table that displays my clients projects, when the project started and when it ended.
On the page i also have a date slicer, so my client can see the projects that are active, for whichever period he wishes to look at.

The measure returns the number of days that the project has been active, in the period that the slicer has determined. E.g. the project is active from monday to friday, but my client is looking at wednesday-friday, then my measure would return 3, for three active days in the given period.

I've done this by writing a series of nested IF statements:
- If the project date start and end are both in the given period, then return the number of days between these.
- If neither are in the given period, return the number of days in the given period.
- If only the project start is in the given period, then return the number of days from project start to the end of the period.
- If only the project end is in the given period, then return the number of days from start of the period, to project end.

Now here's the question
This code gave me a refresh time of around 2500ms. But if i add an IF statement at the start of the measure that returns blank, in case the project date start is blank, then i get a refresh time around 200ms.
Project date start and end are both columns, loaded in from the Query Editor, and neither of them contain any blanks, so i don't understand why this makes the DAX measure significantly faster?

I hope my measure is readable

EXTRA QUESTION:
As you can see in my measure, i have also written some extra IF statements in DATEDIFF whenever i need the number of days from the start of the period or to the end of the period.
That is because in my slicer, when i select todays date (the lowest the slicer can go) or the date 120 days into the future (the highest the slicer can go) power BI doesn't seem to recognise the slicer as actually slicing the data. If i then write FIRSTDATE/LASTDATE(Dim_Calendar[Date]) alone, it would count from the actual first/last date in the entire calendar table, and not the first/last date from the slicer. Know of any workarounds?

Thank you for your help in advance, i appreciate you taking the time, sorry for the long post.

IF(
    [Project Date Start] = BLANK(),
    BLANK(),
    IF(
        [Project Date Start] IN VALUES( Dim_Calendar[Date] ) && [Project Date End] IN VALUES( Dim_Calendar[Date] ),
        /*Project date start and end are both in the period observed*/
        DATEDIFF(
                [Project Date Start],
                [Project Date End],
                DAY
        ),
        IF(
            [Project Date Start] < FIRSTDATE( Dim_Calendar[Date] ) && [Project Date End] > LASTDATE( Dim_Calendar[Date] ),
            /*Neither project dates are in the period observed*/
            DATEDIFF(
                     IF(
                         FIRSTDATE( Dim_Calendar[Date] ) < TODAY(), TODAY(), FIRSTDATE( Dim_Calendar[Date] )
                     ),
                    IF(
                        LASTDATE( Dim_Calendar[Date] ) > TODAY() + 120, TODAY() + 120, LASTDATE( Dim_Calendar[Date] )
                     ),
                     DAY
            ),
            IF(
                [Project Date Start] IN VALUES( Dim_Calendar[Date] ),
                /*Only project date start is in the period observed*/
                DATEDIFF(
                         [Project Date Start],
                         IF(
                             LASTDATE( Dim_Calendar[Date] ) > TODAY() + 120, TODAY() + 120, LASTDATE( Dim_Calendar[Date] )
                         ),
                         DAY
                ),
                /*Only project date end is in the period observed*/
                DATEDIFF(
                                IF(
                                    FIRSTDATE( Dim_Calendar[Date] ) < TODAY(), TODAY(), FIRSTDATE( Dim_Calendar[Date] )
                                ),
                                [Project Date End],
                                DAY
                )
            )
        )
    )
)

[v-yadongf-msft]

Hi @TheSaffar ,

Using a conditional statement in a measure can have a dangerous side effect in the query plan, i.e. the engine always evaluates the branch for each condition regardless of whether the first argument returns True or False. I guess that adding a blank condition to the if statement will reduce the judgment time for each chnditional branch.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.