cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shoeb1359
Frequent Visitor

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

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.

Issue.PNG



1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!