Optimizing a DAX Formula

[Gonqq]

Hi everyone. I'm having trouble with a small dax formula: it consumes a lot of memory and i can't add anymore rows to a mtarix because of it. The formula is the following:

It was one of my first ever formulas on power bi and now im coming back to upgrade it so i can add more rows to a matrix i have. How can i optimize this formula?

Thank you in advance

[v-kkf-msft]

Hi @Gonqq ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[mahoneypat]

I'm surprised this is slow, so I'm assuming you have many combinations of values based on the columns used in the matrix.  I would first look at the query behind the matrix using Performance Analyzer and try to reduce granularity.  If that can't be improved, I would use nested IF functions instead of SWITCH(TRUE().  Your expression forces all 3 variables to be evaluated every time.  Try to nest them so that you eliminate the most early on.

NewExpression =
IF (
    SELECTEDVALUE ( Contas[Grupo Conta] ) <> 62,
    "N/A",
    IF (
        SUM ( Principal[Real] ) > 0,
        "N/A",
        IF (
            SUM ( Principal[PO] ) > 0,
            "N/A",
            GE
        )
    )
)

Pat

[Greg_Deckler]

@Gonqq One way would be to have your GE row be this:

VAR GE = DIVIDE(R; P; "N/Orc")

[Gonqq]

Hi @Greg_Deckler , thank you. I tried it out and it still pulls a lot of memory. I'm almost certain that it's the switch condition that's causing the problem but i really must have that condition.

[Greg_Deckler]

@Gonqq Try: VAR C = IF(gc + R + P = 62, "n.a";GE)

Arithmatic is faster than comparison operators and no need for a SWITCH if it is just a single condition.