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 alt...
Need Help
Need Help - Goals
Performance Issues
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