Forum Discussion
GROUPBY Context Transition
RobertSlattery- I may be off-base here but I believe you have a fundamental misunderstanding of how measures function. For measures in DAX, the starting filter context is ALWAYS the context of the visual. So, if you use a measure in a single row, that's the starting context of the measure unless you change it by wrapping the calculation in a CALCULATE or CALCULATETABLE and using something like an ALL, ALLEXCEPT or other type of filter that fundamental and specifically changes the original filter context.
Your first calculation doesn't do any of that. Your first calculation starts with a filter context of a single row. So, that is the starting filter for the GROUPBY, which is not a context/filter changing function. It is just going to do it's thing on whatever data it has access to based upon the starting context/filter.
Therefore, it is really, really difficult to see exactly what is going on with your functions with what I would consider 1/3rd of the critical information. You have presented the calculations, which is good but we are missing the context in which you are placing them (your use of them in visuals, what visuals, what other information is around them, etc.) And the actual data or a sample of data that is being used so that we can replicate the issue/behavior without recreating it all.
RobertSlattery- I may be off-base here but I believe you have a fundamental misunderstanding of how measures function. For measures in DAX, the starting filter context is ALWAYS the context of the visual. So, if you use a measure in a single row, that's the starting context of the measure unless you change it by wrapping the calculation in a CALCULATE or CALCULATETABLE and using something like an ALL, ALLEXCEPT or other type of filter that fundamental and specifically changes the original filter context.
Your first calculation doesn't do any of that. Your first calculation starts with a filter context of a single row. So, that is the starting filter for the GROUPBY, which is not a context/filter changing function. It is just going to do it's thing on whatever data it has access to based upon the starting context/filter.
Therefore, it is really, really difficult to see exactly what is going on with your functions with what I would consider 1/3rd of the critical information. You have presented the calculations, which is good but we are missing the context in which you are placing them (your use of them in visuals, what visuals, what other information is around them, etc.) And the actual data or a sample of data that is being used so that we can replicate the issue/behavior without recreating it all.
Actualy, apart from all the handwaving about context transition, the reason I had this wron view was because of this answer by v-huizhn-msft , which solved the problem at the time but, I could not understand the explanaition why. Are you able to ttranslate the aswer as to why?
RobertSlattery- Actually yes I believe I can.
Your measure firstDateVisible is using an ALLSELECTED filter, which partially removes original filters but keeps others and I'm not going to try to explain it exactly because it's a weird function. I would reference https://www.sqlbi.com/articles/understanding-allselected/
However, when you put that measure inside of a CALCULATE with a FILTER of ALL, you actually changed the context again. The end effect is that the ALL inside your CALCULATE removed any and all original filters that the ALLSELECTED was preserving.
So, the solution was to use a VAR and essentially grab the value returned from your firstDateVisible measure, preserving the original filter context for ALLSELECTED. Then, once you have that value calculated in the correct context, you can safely use that variable inside of your CALCULATE because it's just a value at that point versus a calculation that is modified by the change in context to ALL.
I hope that makes sense.
"However, when you put that measure inside of a CALCULATE with a FILTER of ALL, you actually changed the context again. The end effect is that the ALL inside your CALCULATE removed any and all original filters that the ALLSELECTED was preserving. "
Greg_Deckler, no way. The measure inside the filter is applied to the ALL(Dates) table, not constrained by it. If it was constrained by the filter it was building then, the whole thing would disappear up it's own arse because that would be a circular reference. The [open orders] measure in the Calculate is affected by the filter but not the [firstDateVisible] one, that would be 'ken crazy. No, I think it's just a bug or an ef'ed if I know moment in the design of the lexicon.
What I'm saying is that your measure here:
firstDateVisible = CALCULATE( FIRSTDATE('Dim Date'[Date]), ALLSELECTED('Dim Date'[Date]) )Note the red parts of that measure. Now, take a look at this measure:
Open Orders Month Opening 2 = CALCULATE( [Open Orders], FILTER(ALL('Dim Date'), 'Dim Date'[Date] < [firstDateVisible] ) )Your measure is within that FILTER clause. The ALL in that FILTER clause removes all of the original context filters because the ALL changes the context to everything in the Dim Date table.
So, again, I can't be certain of exactly what is occurring because I don't have the data and I don't have how you are using this stuff in your visuals, but what I can say is that when [firstDateVisible] executes, the ALLSELECTED is basically meaningless in your second formula because the ALL in your FILTER function has changed the context to essentially everything in that table and thus ALLSELECTED when run in that context is effectively ALL, because everything is technically selected at that moment.
Edit: To be clear on this, if your FIRSTDATE in the context of ALL is January 1st, yyyy and you are in a row context of Feb, then no dates will match your filter because nothing in that context is less than January 1st. Even in January, there is STILL nothing that is less than January 1st. This is why that calculation always return blank would be my guess. If you did <= instead of < my guess is that you would always get whatever value you are calculating for January 1st only when in the context of January but blank for everything else.