CODEWITHSUNDEEP
powerbidax
James Khan
James Khan

Reputation: 841

Second Largest Value in Calculated Column

I am looking to get the second last login for a user from the AccessLog Power BI Table. Using a calculated column to get the last login in the ApplicationUSer table, but also need the second to last login, in a calculated column, if it exists otherwise set to default date. ApplicationUser table is linked to the ApplicationAccessLog table via userid.

Lastest Access Date =
VAR Ho =
    CALCULATE ( MAX ( 'ApplicationAccessLog'[LoginDate] ) )
RETURN
    IF ( ISBLANK ( HO ), 0, HO )

What is the best way to get the second largest value?

The max login and max -1 login needs to be less than or equal to the selected date of my report page as well, so thinking now towards a measure now. If that Selected date is the 25th Dec, then the max login needs to be from the 25th Dec or before and the same with max login -1.

Upvotes: 1

Views: 10061

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40264

Power BI recently added new window functions: OFFSET, INDEX, and WINDOW.

Using INDEX is probably the best way to do this now:

2ndToLast = INDEX ( 2, ALL ( 'Log'[LoginDate] ), ORDERBY ('Log'[LoginDate], DESC ) )

Upvotes: 2

Alexis Olson
Alexis Olson

Reputation: 40264

There are a couple of functions you can rank values with, RANK.EQ / RANKX or TOPN.

Here's an attempt with the latter:

2ndToLast = MINX ( TOPN ( 2, 'Log', 'Log'[LoginDate] ), 'Log'[LoginDate] )

This returns the top two rows sorted by LoginDate and then selects the minimal LoginDate from those two rows that TOPN returns.

Upvotes: 10

Related Questions