Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Performance issues with SUMX

Hi, everyone,

 

I'm working on a dax measure performance, this measure contains as funtion SUMX (As SUMX is an iterator function, it is degrading the performance) and I woul like to use another alternative to fix the performance issue.

 

the measure that I'm working on : 

 

Measure : SUMX(FILTER(VALUES(TABLE1[Column A]),[TAG]<>BLANK()),CALCULATE(DISTINCTCOUNT(TABLE1[Column A])))
 
Thank you for your response. My best regards. 
Need Help
Need Help - Goals
Performance Issues

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    AlB  The measure is supposed to be used for another measure so the measure that I mentioned :                                           

    Measure : SUMX(FILTER(VALUES(TABLE1[Column A]),[TAG]<>BLANK()), CALCULATE(DISTINCTCOUNT(TABLE1[Column A])))  

    Measure* = SUMX(FILTER(VALUES(TABLE1[Column A]),[KLM]>250), IF(ISBLANK([Measure]),BLANK(),[Measure]))

    So the two measures are used to calculate the sum by filtering the values in column A  using another measure like TAG or KLM; for the first is to make a distinctcount and the second one by testing if the first measure is blank or not so if true return Blank and if not return the first measure. 

    will be used in this measure : 

    Measure** = Calculate([Measure*]/[Measure])

     

    For information: TAG KLM Measure* are measures 

    • AlB's avatar
      AlB
      Community Champion
      6 years ago

      Can you show the code for [KLM] and [TAG]? Maybe share the pbix?

      I was asking what [Measure] is supposed to do because I find the code a bit weird:

      Measure =
SUMX (
    FILTER ( VALUES ( TABLE1[Column A] ), [TAG] <> BLANK () ),
    CALCULATE ( DISTINCTCOUNT ( TABLE1[Column A] ) )
)

      you first get the (distinct) rows in column A where [TAG] is non blank. Then you do: 

       

      CALCULATE ( DISTINCTCOUNT ( TABLE1[Column A] ) )

      but that will always yield 1 due to context transition, wont't it? Is that what you intended? So if I understand correctly that code is equivalent to:

      Measure =
COUNTROWS (
    FILTER ( VALUES ( TABLE1[Column A] ), [TAG] <> BLANK () ),
)

       agree?

       

       

      Please mark the question solved when done and consider giving kudos if posts are helpful.

      Cheers  Datanaut

           

       

       

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous 

    What is the measure supposed to do exactly?