IF performance on DAX formula

Question

i am stuck on simple performance problem. i have a RunningBalance formula. I have no problem with this formula. It gives me the last nonempty value of balance. (i have the balance on rows.)

Then i try to write another formula with using this formula. Aim is simple. New formula sould show me nothing(BLANK()) when it is <=0

i tried

RunningBalance:=CALCULATE (
    SUM ( DebitsAndCredits[balance]  );
    LASTNONBLANK (
        'Date'[Day];
        CALCULATE ( SUM (DebitsAndCredits[balance] ) )
    )
)

Newformula:=CALCULATE ( IF ( [RunningBalance] <= 0; BLANK (); [RunningBalance] ) )

i wrote this simple formula. But it is very slow when i use DocumentNumber on rows.

How can i make this formula faster?

Seymour · Accepted Answer · 2020-04-24T15:59:16.580

0

Without variables

I would slightly change the requirement and try to change the IF as:

Newformula:=
MAX(0, [RunningBalance])

Using Variables

A possible enhancement is the following:

RunningBalance:=
VAR _V1 = SUM (DebitsAndCredits[balance] )
RETURN
CALCULATE (
    SUM ( DebitsAndCredits[balance]  );
    LASTNONBLANK (
        'Date'[Day];
        _V1)
    )
)

and,

Newformula:=
VAR _V1 =  [RunningBalance]
RETURN
CALCULATE ( IF ( _V1 <= 0; BLANK (); _V1 ) )

I would also check if the following expression gives you the correct result:

RunningBalance:=
VAR _V1 = SUM (DebitsAndCredits[balance] )
RETURN
CALCULATE (
    _V1;
    LASTNONBLANK (
        'Date'[Day];
        _V1)
    )
)

Otherwise, I would investigate the need of using LASTNONBLANK() and maybe find a different implementation.

edited Apr 24 '20 at 15:59

answered Apr 24 '20 at 15:06

Seymour

3,104
2
22
46

1

Thank you very much for your answer but i can not use VAR function because of my Analysis Server version. :( – Cem Üney Apr 24 '20 at 15:36
It is SQL Server 2012 AS Tabular Edition. And i am using VStudio 2010 to design it. It gives Query (78, 57) Parser: The syntax for '_V1' is incorrect. Error when i try to add your formulas – Cem Üney Apr 24 '20 at 15:41
1

Yikes. DAX without VAR is painful. – Alexis Olson Apr 24 '20 at 15:51
@CemÜney I update my answer. Please check MAX(0, [RunningBalance]) – Seymour Apr 24 '20 at 16:00
"Too many arguments were passed to the MAX function. The maximum argument count for the function is 1." ooops – Cem Üney Apr 24 '20 at 17:16
Can't you update the compatibility version? I mean that is almost 10 years old.. – Seymour Apr 28 '20 at 15:08

IF performance on DAX formula

1 Answers1

Without variables

Using Variables