cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthgunnam
Helper I
Helper I

SUMX Function Degrading the Performance

Hello, I am using a SUMX function in one of my calculation. It is completely degrading the performance of visual. Here is my calculation:

Dummy =
CALCULATE(
              SUMX(DISTINCT(TBL[Account Name]),[Count])
)
In the above calculation count is a measure, which was calculated as follows:
CALCULATE(
              DISTINCTCOUNT(TBL[SaleType])
)
Can you please suggest me a way to improve the performance?
1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

SUMX is iterator, that's why it's so much slower

how about the one below for your Dummy measure

CountUnique = COUNTROWS(SUMMARIZE(TBL,TBL[Account Name],TBL[SaleType]))


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Stachu
Community Champion
Community Champion

SUMX is iterator, that's why it's so much slower

how about the one below for your Dummy measure

CountUnique = COUNTROWS(SUMMARIZE(TBL,TBL[Account Name],TBL[SaleType]))


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Hello @Stachu,

 

I have two tables like TBL and CUST. I have created the CUST table using the DAX.

CUST=
ADDCOLUMNS(
SUMMARIZE(FILTER(TBL,NOT(TBL[Account])=BLANK()),TBL[Account ],
"Sale",CALCULATE(SUM(TBL[Sale Amount]),FILTER(TBL,TBL[Ind]="Yes"),FILTER(TBL,TBL[Ind2]="Yes" && NOT(TBL[Code]) IN {"4"} ))
),
"Grp",
IF ( [Premium] >=0 && [Premium]<100, "0-100",IF ( [Premium] >=100&& [Premium]<500, "100-500",IF ( [Premium] >=500 && [Premium]<1000, "500-1000M",IF ( [Premium] >=1000, "1000+" )))))
Now, I have created a relationship between those two tables using "Account" column and created a Cust Grp column in TBL using Related(CUST[Grp]). I want build a customer count based on grouping. 
Problem:
Counts were fine when there were no filters applied but when I apply a filter on TBL tables Sale amount in CUST table is not filtering(I selected filtering as Both direction). As a result, counts in the group are not being calculated properly. 
For example, if I filter country in TBL to the "USA" the premium in the CUST table is not changing.
 
Please help me out!!!!
Thanks in advance!!!!!

Tables and calculated columns are only calculated once, once you refresh the table that holds them - they are never changing based on the slicer/visual filters

 

if you want to have changing Grp then you need to use a measure instead of calculated column.

If you need help with that I'd suggest creating a new thread with samples from both tables, and marking this one as solved - helps to avoid the clutter on the forum, and may help other user with similar SUMX problems 🙂 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Thank you so much!!! Worked like a charm

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors