Forum Discussion
Issue about CALCULATETABLE
Dear gurus,
I came across an issue which I can't wrap my head around; so I turn to you for help. Thnx in advance!
The senario is fairly simple, which involves only one simple table of dates. I use the date column as slicer; then I'd calculate all dates later than the selected date.
I authored a measures with CALCULATETABLE, which produces a wrong answer.
Wrong =
VAR __date = MAX ( 'Calendar'[Date] )
RETURN
CONCATENATEX (
CALCULATETABLE ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] >= __date ),
'Calendar'[Date],
UNICHAR ( 10 )
)
Then I replace CALCULATETABLE with FILTER, which produces an answer as expected,
Correct =
VAR __date = MAX ( 'Calendar'[Date] )
RETURN
CONCATENATEX (
FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] >= __date ),
'Calendar'[Date],
UNICHAR ( 10 )
)
I understand that in general, context transition occurs while leveraging CALCULATETABLE. Here I didn't manage to figure out which context transit in the calculation. There must be some other reasons resulting in the unexpected answer.🤔
A mockup file is attached for more clarification. Thanks!
CNENFRNL
Yes, CALCULATETABLE creates a context transition but FILTER doesn't. In your case, if you look at the arguments in CALCULATETABLE, after you have applied 'Calendar'[Date] >= __date, you remove filters using ALL ( 'Calendar'[Date] ), that is the reason all dates are in the result. But if you apply VALUES to respect the filters then you can get the correct results with the same formula.Corrected = VAR __date = MAX ( 'Calendar'[Date] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES('Calendar'[Date] ), 'Calendar'[Date] > __date ), 'Calendar'[Date], UNICHAR ( 10 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
2 Replies
CNENFRNL
Yes, CALCULATETABLE creates a context transition but FILTER doesn't. In your case, if you look at the arguments in CALCULATETABLE, after you have applied 'Calendar'[Date] >= __date, you remove filters using ALL ( 'Calendar'[Date] ), that is the reason all dates are in the result. But if you apply VALUES to respect the filters then you can get the correct results with the same formula.Corrected = VAR __date = MAX ( 'Calendar'[Date] ) RETURN CONCATENATEX ( CALCULATETABLE ( VALUES('Calendar'[Date] ), 'Calendar'[Date] > __date ), 'Calendar'[Date], UNICHAR ( 10 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
