Forum Discussion

Zilliox's avatar
Zilliox
Helper IV
5 years ago
Solved

Optimize Dashboard

Hi, I have created several measures, but it is not really optimize. When I publish it, it takes to long before I have the numbers in the Payout visual.   I have done a performance analyze and this...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Actually, I tried one more thing to optimize one of the dependent measures.  Using this one instead gets it under 400 ms.

     

    YTD Commission Payout -1Month =
    VAR vMonths =
        CALCULATETABLE (
            VALUES ( Dates[Year Month] ),
            FILTER (
                ALL ( Dates ),
                Dates[Date]
                    < MIN ( Dates[Date] )
            )
        )
    VAR vSummary =
        ADDCOLUMNS (
            vMonths,
            "cYTDComm", [YTD Commission]
        )
    VAR vMaxMonth =
        MAXX (
            FILTER (
                vSummary,
                [cYTDComm] > 0
            ),
            Dates[Year Month]
        )
    RETURN
        SUMX (
            FILTER (
                vSummary,
                Dates[Year Month] = vMaxMonth
            ),
            [cYTDComm]
        )

     

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Sorry for the delay.  Got busy with work.  Your measure is a better direction; however, I ended up optimizing your original measure.

 

It turns out it is your [YTD Commission Payout -1M] measure that was causing all your trouble.  One simple fix dropped the refresh time of that visual from >26 sec to <2 sec (on my computer).  It probably still could be optimized further but I figure you'll be happy with this progress.

 

Here is the new measure

 

YTD Commission Payout -1Month =
CALCULATE (
    LASTNONBLANKVALUE (
        Dates[Year Month],
        CALCULATE (
            [YTD Commission],
            PREVIOUSMONTH ( Dates[Date] )
        )
    ),
    FILTER (
        ALL ( Dates ),
        Dates[Date]
            <= MAX ( Dates[Date] )
    )
)

 

I probably wouldn't write this measure this way, but all I did was change from Dates[Date] to Dates[Year Month].  FYI that on the way to figuring that out, I had written your original measure differently.  And with the above measure, the two combined are a little faster.

 

 

YTD Commission Payout =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( Dates[Year Month] ),
        "cYTDComm", [YTD Commission],
        "cYTDCommPay-1M", [YTD Commission Payout -1Month],
        "cManCommPay",
            CALCULATE (
                SUM ( 'Manual Commission Payout'[Amount] )
            )
    )
RETURN
    SUMX (
        vSummary,
        IF (
            ISBLANK ( [cYTDComm] ),
            BLANK (),
            [cYTDComm] - [cYTDCommPay-1M] + [cManCommPay]
        )
    )

 

It's still over 1.5 sec to refresh that visual, but I didn't have more time to spend on it.

 

Regards,

Pat