cancel
Showing results for
Did you mean:
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
Community Champion

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

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

Thank you for the kudos 🙂

Proud to be a Super User!

4 REPLIES 4
Community Champion

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

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

Thank you for the kudos 🙂

Proud to be a Super User!

Helper I

Hello @Stachu,

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

CUST=
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",
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.

Community Champion

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 🙂

Thank you for the kudos 🙂

Proud to be a Super User!

Helper I

Thank you so much!!! Worked like a charm

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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 Solution Authors
Top Kudoed Authors