DAX Variables used in formula

[dicken]

Hi,
I have a question re variables, which i tend to use as it makes it easier to understand but in the formula I have for a running total in a calculated column
I have to use a var or it won't work;

VAR aitem = AllandValues[item]
RETURN
    CALCULATE (
        [avsales],
        FILTER (
            ALL ( AllandValues ),
            AllandValues[dex] <= EARLIER ( AllandValues[dex] )
                && CALCULATETABLE ( VALUES ( AllandValues[item] ) ) = aitem
        )
    )

the item (aitem) has to be declared as a variable otherwise you just get a running total. So what's happening?

Richard



Richard

 

[Matt Allington]

If you put the column inside the FILTER function, it is inside the row context of filter. By defining it as a variable as you have, it is referenced outside of the row context of filter (but in the row context of the column). If you want to replace the variable and just use the column, you need to wrap the column inside EARLIER() to escape the filter row context.

 

[dicken]

If you put the column inside the FILTER function, it is inside the row context of filter. By defining it as a variable as you have, it is referenced outside of the row context of filter (but in the row context of the column). If you want to replace the variable and just use the column, you need to wrap the column inside EARLIER() to escape the filter row context.

Thanks, I may have to re read that several times,
this actually came about as I made a mistake and meant to use;

 

[dicken]

If you put the column inside the FILTER function, it is inside the row context of filter. By defining it as a variable as you have, it is referenced outside of the row context of filter (but in the row context of the column). If you want to replace the variable and just use the column, you need to wrap the column inside EARLIER() to escape the filter row context.

Thanks, I may have to re read that several times,
this actually came about as I made a mistake and meant to use;

VAR currentd = Table1[Date]
RETURN
    CALCULATE (
        [Tsales],
        FILTER ( ALL ( Table1 ), Table1[Date] <= currentd ),
        CALCULATETABLE ( VALUES ( Table1[Product] ) )
    )

One thing I have found is that if ALLEXCEPT and ALL and VALUES when used in a measure similar to the above column,
ALL and VALUES will return a running total that respects the 'Product' even if 'Product' is not present to the pivot table, so if just a date column is present , but ALLEXCEPT does not it behaves pretty much like a 'normal' running total measure,
I don't know if you can recommend anything to read most of what I find is using them to create a given denominator for percentages, I have make up a p table showing both examples so may cross post with forum, but would be interested in any comments on this behavior.

Richard.

 

[dicken]

If you put the column inside the FILTER function, it is inside the row context of filter. By defining it as a variable as you have, it is referenced outside of the row context of filter (but in the row context of the column). If you want to replace the variable and just use the column, you need to wrap the column inside EARLIER() to escape the filter row context.

Sorry , know this is third response, I've tried wrapping column in EARLIER and works, this of course means I now have a lot more to re think EARLIER , have you seen chris webbs blog where he shows how to use it in a running total measure? I've got it copied for reference, you may understand it.
RD