Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@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 

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
Super User
Super User

Hi @Anonymous 

What is the measure supposed to do exactly?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors