Find out the second last progress Id with respect to each User id

[shoeb1359]

Hi,

Need your help !!

For every user_id there are multiple Progress_id. I am trying to find out the second last progress_id with respect to each user_id. I want to create a column as Result which will have the second last progress_id . Screenshot below.

[amitchandak · Accepted solution]

@shoeb1359 , Try a new measure like

Measure =
VAR __id = MAX ('Table'[user_ID] )
VAR __date = CALCULATE ( MAX('Table'[progress id] ), ALLSELECTED ('Table' ), 'Table'[user_ID] = __id )
CALCULATE ( MAX ('Table'[progress id] ), VALUES ('Table'[user_ID] ),'Table'[user_ID] = __id,'Table'[progress id] < __date )

new column =
var _max = maxx(filter(Table, [user_ID] =earlier([user_ID]) ),[progress id])
return
maxx(filter(Table, [user_ID] =earlier([user_ID]) && [progress id] <_max ),[progress id])

[shoeb1359]

Thanks, Amit for the Solution. Surely I will Implement this.
Although one solution I figured out earlier, please suggest this approach feasible in terms of performance.

Rank =
RANKX (
FILTER (
table,
table[User_ID] = EARLIER ( table[User_ID] )
),
table[Progress ID],
,
ASC
)

Another column : 
Progress id_2=
CALCULATE (
MAXX(
table,
CALCULATE(
SUM(table[Progress ID]),
FILTER(
table,
table[User_ID] = EARLIER (table[User_ID] )
&& table[Rank]
=EARLIER ( table[Rank] )-1
),
ALLEXCEPT(table,table[User_ID])
)
),
ALLEXCEPT(table,table[User_ID])
)

Third column: 
Previous profile = IF(table[Progress id_2]=BLANK(),table[Teacher_ID],table[Progress id_2])