cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamprajot
Responsive Resident
Responsive Resident

This DAX formula is not working.

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.

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@iamprajot,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Responsive Resident
Responsive Resident

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))

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors