Forum Discussion

VendettaBob's avatar
VendettaBob
Helper II
4 years ago

DAX Slow Formula

Hi,

 

I have a DAX expression which buckets data based on dynamic filters. The measure takes over 30 seconds to run:

 

 CALCULATE(VALUES(CPOScoreBucket[Score])
,FILTER(CPOScoreBucket,[Total GPR] >= CPOScoreBucket[FromRange] 
&& [Total GPR] <CPOScoreBucket[ToRange] 
&& CPOScoreBucket[Criteria] = "GrossProfitMargin"))

 


I was thinking I could speed this up by doing something like:

 

var SummaryTable = 
        SUMMARIZECOLUMNS(
          //'CPOScoreBucket'[Score],
          'CPOScoreBucket'[ToRange],
          'CPOScoreBucket'[FromRange],
          'CPOScoreBucket'[Criteria],
          FILTER(CPOScoreBucket,'CPOScoreBucket'[Criteria] = "GrossProfitMargin" && [Total GPR] >= CPOScoreBucket[FromRange] && [Total GPR] < CPOScoreBucket[ToRange] ),
          "Score",MAX('CPOScoreBucket'[Score])
        	)
 
RETURN MAXX(SummaryTable,[Score])

 


However, this doesn't work.

Does anyone have any suggestions?
 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    VendettaBob , the first one seems good.  Also, check performance of Total GPR and check if that can improve

     

    use performance analyzer

  • croberts21's avatar
    croberts21
    Continued Contributor
    4 years ago

    What's your data source? Is it a database? If you use criteria on fields in a db those fields should probably be indexed. For example, an invoicedate field is something I would search on a lot so I would want an index on it.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi VendettaBob ,

     

    Your DAX cache builds up from internal VertiPaq queries. You can clear your cache from within DAX Studio. Resetting your cache enables you to effectively measure performance gains.


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