How to OPTIMISE SUMX and CROSSJOIN Measure

[Deevo_]

Hi All,

I have this measure which is used to sum up only the positive numbers that are a result from a measure.

How can I improve this formula below?

SUMX (

    CROSSJOIN ( VALUES('WBS Codes'[PSCode]), VALUES( 'Teams'[Division]), VALUES('Teams'[Branch]), VALUES('RoleNames'[RoleName]), VALUES(SpecialisationList[SpecialisationName])

    ),

    IF ( [Required Project Effort (Hrs)] - [Assignment (Hrs)] > 0, [Required Project Effort (Hrs)] - [Assignment (Hrs)], BLANK () )

)

Additional info:

• All the 'VALUES()' in the crossjoin are all the different Tables and fields used in my matrix visual.
• This formula is used to find the difference (either positive or negative values) 
  • Required Project Effort (Hrs)] - [Assignment (Hrs)]

Thanks in advance

[Deevo_]

Thanks @tamerj1 for your help with this one! I believe i will have to rethink the requirements for this matrix visual because it is still quite slow to process when all values are selected from the slicer. I have tried to remove a couple of the columns in the matrix and instantly this renders the visual alot more quicker. 

I really appreciate your help and time!

[tamerj1]

Hi @Deevo_ 
Not much can be done without at least an overview of the data model, the report visual and the DAX of the nested measures. However, we may try to avoid an expensive callback and rather try to push the calculation back to the storage engine.

Formula =
VAR FinalResult =
    SUMX (
        CROSSJOIN (
            VALUES ( 'WBS Codes'[PSCode] ),
            VALUES ( 'Teams'[Division] ),
            VALUES ( 'Teams'[Branch] ),
            VALUES ( 'RoleNames'[RoleName] ),
            VALUES ( SpecialisationList[SpecialisationName] )
        ),
        VAR Result = [Required Project Effort (Hrs)] - [Assignment (Hrs)]
        RETURN
            Result * ( Result > 0 )
    )
RETURN
    IF ( FinalResult > 0, FinalResult )

[Deevo_]

Hi Tamerj1,

Thank you for replying. Your changes works a treat. I am using this with a slicer for Project Code, If i select all codes in the slicer, i receive an error "There not enough memory to complete this operation. Please try again later when there may be more memory available". I believe this is because I have 5 columns in the CROSSJOIN from different tables and this creates results for all the different combinations.

Can you think of another way to modify the measure to allow correct summation of the positive numbers only and not take up so much memory?

I can post some screenshots with the fields blanked out. I hope this helps!

The DAX of the nested measures are:

• [Required Project Effort (Hrs)] = SUM('Required Project Effort'[Effort Hours per day])
• [Assignment (Hrs)] = SUM('Assignments'[Effort Hours per day])

Datamodel:

Matrix Table:

[tamerj1 · Accepted solution]

Hi @Deevo_ 
To be honest, I've never encountered the very same situation. This is a bit different as 2 fact tables are involved and unless I do some testing I won't be able to provide a concrete conclusion. It is dificult in some cases to anticipate the shape of the query plan that would be generated out of the DAX code feed to the engine.
The following code might be evaluated at two stages where the storage engine is able to generate the crossjoin combinations that produces positive values then this list is fed up to the next query which will compute the expression again but only for these combinations. In this case if the number of relevant combinations is too low that could work fine. Otherwise the performance would be worse. 
The other possibility is that the storage engine produces an internal batch and computes everything internally while sending the final result only to the formula engine.

Formula =
SUMX (
    FILTER (
        CROSSJOIN (
            VALUES ( 'WBS Codes'[PSCode] ),
            VALUES ( 'Teams'[Division] ),
            VALUES ( 'Teams'[Branch] ),
            VALUES ( 'RoleNames'[RoleName] ),
            VALUES ( SpecialisationList[SpecialisationName] )
        ),
        [Required Project Effort (Hrs)] - [Assignment (Hrs)] > 0
    ),
    [Required Project Effort (Hrs)] - [Assignment (Hrs)]
)