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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LLT1021
Helper I
Helper I

SUMX

Dear all,
 
I have a question regarding SUMX in relation with memory issues. 

I made a small table in which is displayed amount A, an amount B and C=MAX(A-B,0) for key users of a system.
These measures give the right results except the totalling row of C. Therefore I made a fourth measure SUM CORRECT = SUMX(VALUES(EMPLOYEEE[KEY]),C). This Employee table is a separate table in which all available values for the key are included. As can be seen, this measure works fine for the selection of users below; it gives the same results as C but now with the correct total. 
The only thing is, if I run SUM CORRECT over all key users, it takes something like 5 minutes and then I got the notification that there is not enough memory to complete the operation. Any ideas how to fix this? Thanks a lot for your help!

LLT1021_2-1675969773019.png

 

 

10 REPLIES 10
LLT1021
Helper I
Helper I

Exactly! That's why I created the 'SUM CORRECT' measure, which takes that into account (as you can see in the table), but then I have the problem of memory issues.

 

JirkaZ
Solution Specialist
Solution Specialist

How many records are there in the Employee table?

There are 3150 records in that table.

JirkaZ
Solution Specialist
Solution Specialist

That's not too much to be honest. Your formula should work just fine. Is it failing on the Desktop or in the service as well? 

Hmm ok, I just tried also in service and it gives the same problem unfortunately - the visual has exceed the available resources.. 

JirkaZ
Solution Specialist
Solution Specialist

Might be related to how the measures A and B are defined then.

A = 

CALCULATE (
    SUM ( 'Fact'[Hours] ),
    'Fact'[Soort] <> "Niet toegewezen"
                   )

B = divide( 
CALCULATE (
    TOTALYTD ( SUM ( 'Fact'[Start right] ), Datumtabel[Datum] ),
    'Fact'[soort] = "AV"
),
12
)
JirkaZ
Solution Specialist
Solution Specialist

So I'd try with a subset of employees only and run the Performance Analyzer to see what exactly is taking so much resources/time. Could be the time intelligence calculation.

For small subset of employee, it looks like that function A takes quite some time relatively, but are there ways to make this measure more efficient you think?

JirkaZ
Solution Specialist
Solution Specialist

But your definition of C doesn't say anything about summing. It just says "take the diff of A and B and if it's greater than 0, use it". And that's exactly what it does in the totals too. Total of A is lower than the total of B, hence 0 is being used.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors