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

There are multiple things wrong with that measure and the ones it depends on.  There are nested iterators, measures inside iterators some using IF( ) which all add up to poor performance.  Can you explain what that measure is supposed to do, so a new measure can be proposed starting from scratch I think?

Regards,

Pat

  • Zilliox's avatar
    Zilliox
    Helper IV
    5 years ago

    This is really kind of you.

     

    The result is commission payment.

    It is based on the YTD achievement. Each person has a different (Query OTE) (this is the yearly commission).

    If it YTD achievement is lower than 80% it has to be 0. If higher than 100% than it stays at 100%. The amount has to be in the currency of the person.

     

    The image below is the result I am looking for, but then with a better measure

     

    Thanks again