This DAX formula is not working.

[iamprajot]

Result =
VAR SecondLast = CALCULATE(MAX(Data[Units]),FILTER(ALL(Data[Units]),Data[Units]<MAX(Data[Units])))
RETURN
CALCULATE(VALUES(Data[Units]),FILTER(ALL(Data),Data[Units]=SecondLast))

In SecondLast I am trying to get the second Max Unit available

& finally trying to get the second Max Unit available for whatever the selection I have put in from other columns e.g. Items, Dates etc

But seems like there is no result (blank) in this SecondLast variable and hence no result at all.

[v-chuncz-msft]

@iamprajot,

Things are harder when you have nested CALCULATE statements. You may try using RANKX Function instead.

[iamprajot]

Thanks but what I am curious about is that it is working without the columnname and not with it.

ALL(Data[Units]) - not working

ALL(Data[Units]) - removing column name worked

[iamprajot]

Removing Column Name and keeping the Table Name did the thing, but WHY and HOW ? It doesn't matter if I keep whole Table or a single Column.

Result =
VAR SecondLast = CALCULATE(MAX(Data[Units]),FILTER(ALL(Data[Units]),Data[Units]<MAX(Data[Units])))
RETURN
CALCULATE(VALUES(Data[Units]),FILTER(ALL(Data),Data[Units]=SecondLast))