Forum Discussion

aross0602's avatar
aross0602
Frequent Visitor
2 years ago

Slow Measures

I have created 3 simple measures:

Sales = SUM('Transactions'[RevenueExtended])
Transactions = DISTINCTCOUNT(Transactions[OrderId])
Avg Ticket = DIVIDE([Sales], [Transactions])
 
These all load quickly with no issues in my table visual.  From there, I have these measures:
Retail Sales = CALCULATE([Sales], FILTER('Transactions', [SalesChannel] = "IN-STORE")) --loads quickly
Retail Transactions = CALCULATE([Transactions], FILTER('Transactions', [SalesChannel] = "IN-STORE")) --loads slowly
Retail Avg Tkt = CALCULATE([Avg Ticket], FILTER('Transactions', [SalesChannel] = "IN-STORE")) --loads slowly
 
I have a aggregate table that includes the sales and transaction count, as well.  That is not speeding up the latter two measures, and I cannot determine the issue.
 
Need Help
Tips and Tricks

6 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    2 years ago

    Hi aross0602 

    Did you use Performance analyzer to determine part of the query is taking too long?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi aross0602 ,

    Everytime the measure is called in an expression, the filter and/or context may be different.  That is why it needs to be evaluated separately each time.

    Paste the other measures that are referenced in the final measure, we may be able to optimize those as well.

    try this:

    Retail Transactions = 
     var Transactions = DISTINCTCOUNT('Transactions'[RevenueExtended])
     return
        calculate(Transactions,Filter('Transactions',[SalesChannel]="IN-STORE"))

    Best Regards,

    Xianda Tang

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

    • aross0602's avatar
      aross0602
      Frequent Visitor
      2 years ago

      Hi, 

      Did you intend to use RevenueExtended instead of OrderId in the var expression?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi aross0602 ,

        My suggestion is just to try not to load measures inside calculate function, since the filter and/or context may be different each time the metric is called in the expression

        Best Regards,

        Xianda Tang

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