DAX measure to return the Second largest true value

[balafbm]

I have this Date table in my data model.

Date ID	Date as Integer	Business Day
3/28/2023	20230328	True
3/29/2023	20230329	True
3/30/2023	20230330	True
3/31/2023	20230331	True
4/1/2023	20230401	False
4/2/2023	20230402	False
4/3/2023	20230403	True

I would like to return the second highest "True" Business Day - Date

In the above case, the measure should return 3/31/2023

The DAX for the MAX or the highest "True Business Day" is quite simple.

CALCULATE(MAX('Date'[Date ID]),ALL('Date'),'Date'[Business Day] = TRUE())

The above DAX will return "4/3/2023"

However, I can't quite figure out how to get the second highest "True" Business Day - Date: that is " 3/31/2023"
Any help will be appreciated.

[tamerj1 · Accepted solution]

Hi @balafbm 

please try

=
MINX (
TOPN (
2,
FILTER ( ALL ( 'Date' ), 'Date'[Business Day] = TRUE () ),
'Date'[Date]
),
'Date'[Date]
)