Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate with and without variables difference

I have 2 calculate formulas:  Formula 1:  Emp Exits =         CALCULATE(             SUM(Emp_Exits[Employee Exits]),             Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]),             ALL(E...
"CALCULATE"
variables
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    The measure

    Emp Exits with variable =
VAR EmpExits =
    SUM ( Emp_Exits[Employee Exits] )
RETURN
    CALCULATE (
        EmpExits,
        Emp_Exits[MonthNo] <= MAX ( Emp_Exits[MonthNo] ),
        ALL ( Emp_Exits[Month Name] )
    )

    is exactly the same as 

    Emp Exits with variable =
SUM ( Emp_Exits[Employee Exits] )

    The reason is that variables are only evaluated once therefore cannot be recalculated as measures inside a CALCULATE statement. Thus only the value of the first measure is always returned.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
The measure

Emp Exits with variable =
VAR EmpExits =
    SUM ( Emp_Exits[Employee Exits] )
RETURN
    CALCULATE (
        EmpExits,
        Emp_Exits[MonthNo] <= MAX ( Emp_Exits[MonthNo] ),
        ALL ( Emp_Exits[Month Name] )
    )

is exactly the same as 

Emp Exits with variable =
SUM ( Emp_Exits[Employee Exits] )

The reason is that variables are only evaluated once therefore cannot be recalculated as measures inside a CALCULATE statement. Thus only the value of the first measure is always returned.