Performance issues with SUMX

[Anonymous]

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. 

[Anonymous]

@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]

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  

[AlB]

Hi @Anonymous 

What is the measure supposed to do exactly?