Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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[Accou...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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]))
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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]))
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!!!!!
  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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 :)