SUMX

[LLT1021]

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]

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]

How many records are there in the Employee table?

[LLT1021]

There are 3150 records in that table.

[JirkaZ]

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? 

[LLT1021]

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

[JirkaZ]

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

[LLT1021]

A = 

CALCULATE (

    SUM ( 'Fact'[Hours] ),

    'Fact'[Soort] <> "Niet toegewezen"
                   )

B = divide( 

CALCULATE (

    TOTALYTD ( SUM ( 'Fact'[Start right] ), Datumtabel[Datum] ),

    'Fact'[soort] = "AV"

),
12
)

[JirkaZ]

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.

[LLT1021]

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]

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.