cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hicham Member
Member

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. 
3 REPLIES 3
Super User III
Super User III

Re: Performance issues with SUMX

Hi @Hicham 

What is the measure supposed to do exactly?

Hicham Member
Member

Re: Performance issues with SUMX

@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 

Highlighted
Super User III
Super User III

Re: Performance issues with SUMX

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

     

 

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors