Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
balafbm
Frequent Visitor

DAX measure to return the Second largest true value

I have this Date table in my data model.

Date IDDate as IntegerBusiness Day
3/28/202320230328True
3/29/202320230329True
3/30/202320230330True
3/31/202320230331True
4/1/202320230401False
4/2/202320230402False
4/3/202320230403True

 

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.

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @balafbm 

please try

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

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @balafbm 

please try

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors