GROUPBY Context Transition

[RobertSlattery]

When I use this measure in a row context with one date, the row context is transfered to the filter and the sum calculated is just for the current date.  Why?  In the documentation it says the row context is NOT tranfered.

Order Value Prev Day = 
    VAR ol = GROUPBY(
        orderLines,[DATE],
        "Sales Signed", SUMX(CURRENTGROUP(), [Amount] * RELATED(Order_Types[Sign])))
    RETURN
    SUMX(ol, [Sales Signed])

And then, when I use ALL() to remove the filter context and add a virtual join,

Order Value Prev Day = 
    VAR ol = CALCULATETABLE(
        GROUPBY(
        orderLines,[DATE],
        "Sales Signed", SUMX(CURRENTGROUP(), [Amount] * RELATED(Order_Types[Sign]))),
        ALL(orderLines)
    )
    RETURN
//    SUMX(ol, [Sales Signed])/*
    CALCULATE(
        SUMX(ol, [Sales Signed]),
        FILTER(
            ol,
            [DATE] = MAX('Dim Date'[Date]) - 1
        )
    )

 I don't get a filter on SUMX(ol, [Sales Signed]).  the ol table is not filtered.

Why not?

I can work arround this as shown below but, I'd like to understand what is going on.  Can anybody enlighten me?

Order Value Prev Day = 
    VAR ol = CALCULATETABLE(
        GROUPBY(
        orderLines,[DATE],
        "Sales Signed", SUMX(CURRENTGROUP(), [Amount] * RELATED(Order_Types[Sign]))),
        ALL(orderLines)
    )
    RETURN
    CALCULATE(
        SUMX(ol, IF([DATE] = MAX('Dim Date'[Date]) - 1, [Sales Signed]))
    )

[Greg_Deckler]

First question, can we get sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second question, where in the documentation do you read that row context is not passed through? https://msdn.microsoft.com/en-us/query-bi/dax/groupby-function-dax That doesn't really make sense to me and I don't see it in the documentation.

[RobertSlattery]

Hi @Greg_Deckler, Yes I know, but since I have a work-arround, I'll have to post an example later when I get time.  Having said that, I would have thought that context transition or not on a particular function - outside of a Calculate - would be a straight-up knowledge thing.  (And since MS doesn't bother to document properly, we have to create knowledge by posting questions.)

Most functions do not apply a context transition outside of Calculate or CalculateTable so I find it surprising that GROUPBY does, especially given the rmarks in the documentation here

 

I don't know where to look in the docs bout if FILTER fails silently on a table dynamically created within a measure but again this is a specific question about a specific scenario that doesn't require data I think.

[Greg_Deckler · Accepted solution]

@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]

@Greg_Deckler

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?

[Greg_Deckler]

@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. 

[RobertSlattery]

"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.

[Greg_Deckler]

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.

[RobertSlattery]

Youre right @Greg_Deckler, the context is there regardless of the Calculate.  I'm a bit confused about this, given all the mysterious handwaving about Calculate efecting a ~Context Transition~.  SMH.

OK, thanks again.

[Greg_Deckler]

Correct, the CALCULATE just gives you the option of changing the context/filters of the calculation. So, if you use something like FILTER then you are taking the existing context from the visual that is coming into the calculation and becoming more selective. Alternatively, you can use things like ALL and ALLEXCEPT to remove context and then, potentially add context/filters back to it.

Understanding DAX hinges on understanding the concept of context and paying close attention to the context in which the code is running.