Result =VAR SecondLast = CALCULATE(MAX(Data[Units]),FILTER(ALL(Data[Units]),Data[Units]<MAX(Data[Units])))RETURNCALCULATE(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.
Things are harder when you have nested CALCULATE statements. You may try using RANKX Function instead.
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
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.
Click here to learn more about the September 2022 updates!
Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!