Forum Discussion
Get the second last value for same id's coming in multiple rows
Hi, I need help to fetch 2nd last date and status from the table but the table consists of multiple rows of the same IDs. So if there 3 same IDs its last status is EXPIRED but the 2nd last status can be SEEN or SENT or anything else so I want to fetch that and I want to fetch that dynamically because my data gets refreshed every day so status can be changed. Here is an example of the table.
As you can see for ID (5d108efe3389bb72d7cff1d8) there are two values (SENT) and (Expired) and I want SENT and date in two new different columns.
Please if anyone can help me with this problem.
Hi, shubham-sharma
According to your description, I can roughly understand your requirement, I created some test data and achieved your requirement, I think you can try my steps:
This is my test data:
- I created two calculated columns in the main table:
Appear times = RANKX(FILTER(ALL('Table'),[_id]=EARLIER('Table'[_id])),[activityLog.loggedAt],,ASC,Dense)Total appear times = COUNTX(FILTER(ALL('Table'),[_id]=EARLIER('Table'[_id])),[activityLog.loggedAt])- I created a calculated table like this:
Expected output = SELECTCOLUMNS(FILTER('Table',[Appear times]=[Total appear times]-1),"ID",[_id],"Date",[activityLog.loggedAt],"Status",[activityLog.status])And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-robertq-msft
Community Support
Hi, shubham-sharma
According to your description, I can roughly understand your requirement, I created some test data and achieved your requirement, I think you can try my steps:
This is my test data:
- I created two calculated columns in the main table:
Appear times = RANKX(FILTER(ALL('Table'),[_id]=EARLIER('Table'[_id])),[activityLog.loggedAt],,ASC,Dense)Total appear times = COUNTX(FILTER(ALL('Table'),[_id]=EARLIER('Table'[_id])),[activityLog.loggedAt])- I created a calculated table like this:
Expected output = SELECTCOLUMNS(FILTER('Table',[Appear times]=[Total appear times]-1),"ID",[_id],"Date",[activityLog.loggedAt],"Status",[activityLog.status])And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.