DAX Tuning a Measure that Requires a Filter

Dec 15, 2016 at 8:55 PM
I hope this is the right place for this question. Please let me know if not.

I am trying to performance tune the measure below and I am new to DAX. The Fiscal Year To Date Sales measure has logic to make sure the Excel user is filtered down to 1 date/fiscal year before it will show results.

When using this tool, what's the best way to emulate a user having picked a date (without adding more performance variables) so I can test performance ? I am considering removing the IF statement to gain performance at the risk of a user picking more than 1 date.
EVALUATE
ROW (
    "Results", IF (
        HASONEVALUE ( 'Invoice Date'[Fiscal Year] ) && MAX ( 'Invoice Date'[Date] ),
        CALCULATE (
            [Sales],
            ALL ( 'Invoice Date' ),
            FILTER (
                ALL ( 'Invoice Date' ),
                'Invoice Date'[Fiscal Year] = VALUES ( 'Invoice Date'[Fiscal Year] )
                    && 'Invoice Date'[Date] <= MAX ( 'Invoice Date'[Date] )
            )
        ),
        BLANK ()
    )
)
Looking forward to learning more about this great tool, thanks !
Dec 19, 2016 at 6:54 PM
Edited Dec 19, 2016 at 6:55 PM
Some additional info: I think i got this to work so I could compare including the IF/HASONEVALUE vs. Not including that logic.
EVALUATE
ROW (
    "Results", IF (
        HASONEVALUE ( 'Invoice Date'[Fiscal Year] ) && MAX ( 'Invoice Date'[Date] ),
        BLANK (),
        CALCULATE (
            [Sales],
            ALL ( 'Invoice Date' ),
            FILTER (
                ALL ( 'Invoice Date' ),
                'Invoice Date'[Fiscal Year] = MAX ( 'Invoice Date'[Fiscal Year] )
                    && 'Invoice Date'[Date] <= MAX ( 'Invoice Date'[Date] )
            )
        )
        
    )
Not including:
EVALUATE
ROW (
    "Results", 
        CALCULATE (
            [Sales],
            ALL ( 'Invoice Date' ),
            FILTER (
                ALL ( 'Invoice Date' ),
                'Invoice Date'[Fiscal Year] = MAX ( 'Invoice Date'[Fiscal Year] )
                    && 'Invoice Date'[Date] <= MAX ( 'Invoice Date'[Date] )
            )
        )
    )
The interesting part is, leaving the IF/HASONEVALUE is showing typically slightly faster in total milliseconds ran. We already made this change in our production cube and noticed HUGE performance gains when drilling through Excel, so I'm a little confused. I am sure there is a good reason. Is it because this measure is returning 1 value in these tests, but in Excel, i'm drilling down in a typical Location, SalesRep, Customer hierarchy ?