Forum Discussion
Why using the VARiable changes the result
Can someone please explain to me the expected difference in these two DAX measures. One contains a variable (VAR) "SpendSum" and the other does not. Needless to say I was getting different results with my associated visuals. They are both one of twelve Monthly values to be displayed for the selected year, but the one using the variable unexpectedly resulted in a table with all monthly results being the same annual total (for each respective row). The one without the variable displayed each respective individual monthly value as desired. I thought the VAR just took the place of the function for ease of coding, but there was a different result. Why it worked that way? Just trying to understand how VARs work and their limitations. Thank you in advance for your explanations.
MEASURE 1 =
VAR SelectYear = SELECTEDVALUE('Calendar Table 2'[Year])
RETURN
Calculate(SUMX('Spend',' Spend'[Amount USD]), (DATESBETWEEN('Calendar Table 2'[DATE].[Date], DATE(SelectYear,1,1), DATE(SelectYear,1,31))), ('Spend'[CATEGORY]<>"Spend Delta"))
------------------------------------------------------------------
MEASURE 2 =
VAR SelectYear = SELECTEDVALUE('Calendar Table 2'[Year])
VAR SpendSum = SUMX('Spend','Spend'[Amount USD])
RETURN
Calculate(SpendSum,(DATESBETWEEN('Calendar Table 2'[DATE].[Date], DATE(SelectYear,1,1), DATE(SelectYear,1,31))), ('Spend'[CATEGORY]<>"Spend Delta"))
2 Replies
In Measure 1, SUMX is inside CALCULATE, so it's evaluated under the filter context created by CALCULATE. The context is the specific month set by DATESBETWEEN.
In Measure 2, SUMX is calculated outside of CALCULATE (in the variable SpendSum), so it sums over the entire table without the month-specific filter context. Then, CALCULATE doesn't affect the value of SpendSum since it's already computed. It's like having a constant value.