DAX Measure used in Formula produces blank but not when variable

[dicken]

This is cross posted with Excel Forum; DAX Using a Measure in formula produces a blank and can't see why

My question relates to the following formula to get the previous value from non consec dates; first I have a filtered down table.

Filtered Dates :=
MAXX (
    FILTER (
        ALL ( Table1[Date] ),
        Table1[Date] < IF ( HASONEVALUE ( Table1[Date] ), VALUES ( Table1[Date] ) )
    ),
    Table1[Date]
)

and then ;

M as VAR Previous Unit :=
VAR FDs = [Filtered Dates]
VAR PrevU =
    CALCULATE ( [Tsales], FILTER ( ALL ( Table1[Date] ), Table1[Date] = FDs ) )
RETURN
    PrevU

Can someone explain why I have to declare the Filtered dates measure as a variable, if I use it directly, I get a blank, I've tried using studio to get an idea
of what's happening but without much success.

Richard.

 

[Matt Allington]

You first formula is very inefficient

try this

Filtered Dates :=
MAXX (
ALL ( Table1[Date] ),
Table1[Date]
)
in your second formula, I *** you are saying that the alternative is to replace FDs with the measure. If you do that, you are generating a row context inside FILTER. FILTER steps through every date, one at a time. The measure triggers context transition (which does not happen when you declare the variable first. I’m not really sure what happens next because as the first formula is pretty weird to me.

 

[dicken]

the purpose is to return the previous row value, based on date, nonconsecutive, so the table is fired down to the maximum date value that is below the current date value and then use calculate to return the 'unit' and that row.

I did find that this work ;

Copy of FilteredTable2 :=
VAR FDs =
    VAR mdate =
        MAX ( Table1[Date] )
    RETURN
        MAXX ( FILTER ( ALL ( Table1[Date] ), Table1[Date] < mdate ), Table1[Date] )
VAR anser =
    CALCULATE ( [Tunits], FILTER ( ALL ( Table1[Date] ), Table1[Date] = FDs ) )
RETURN
    anser

The original use of "if hasovne value" , was because that gives the current row date in the pivot, you can then count the number of dates below the current row date and from there take the the maximum of that, Anyway I sort of get what you're saying about declaring the variable as a measure, as much as I ever will.
just to add one slight disadvantage of not using if hasonvvalue is that you end up with a value in total, and still have to use isfiltered or something to get rid of it.

Richard.

 

[Matt Allington]

Copy of FilteredTable2 :=
VAR FDs =
    VAR mdate =
        MAX ( Table1[Date] )
    RETURN
        MAXX ( FILTER ( ALL ( Table1[Date] ), Table1[Date] < mdate ), Table1[Date] )
VAR anser =
    CALCULATE ( [Tunits], FILTER ( ALL ( Table1[Date] ), Table1[Date] = FDs ) )
RETURN
    if(hasonevalue(Table1[Date]),anser)

OK, but HASONEVALUE is better placed outside of the MAXX

 

[dicken]

Copy of FilteredTable2 :=
VAR FDs =
    VAR mdate =
        MAX ( Table1[Date] )
    RETURN
        MAXX ( FILTER ( ALL ( Table1[Date] ), Table1[Date] < mdate ), Table1[Date] )
VAR anser =
    CALCULATE ( [Tunits], FILTER ( ALL ( Table1[Date] ), Table1[Date] = FDs ) )
RETURN
    if(hasonevalue(Table1[Date]),anser)

OK, but HASONEVALUE is better placed outside of the MAXX

Thank you, sorry for slow response, not been too well last few days, still aren't for that matter.

Richard