Forum Discussion
Last Date for Distinct User and Item Modified
Hello all,
I have a table with columns "Task Modified By", Task Modified On", "Activity Modified By", "Activity Modified On","Process Modified By" and "Process Modified On". The "...By" columns are names that are not unique and the "...On" columns are dates that are not unique either. Here is an example in Excel:
| Activity Last Modified By | Activity Last Modified On | Task Last Modified By | Activity Last Modified On | Process Modified By | Process Modified On |
| John | 11/20/2019 | Aaron | 9/15/2019 | John | 10/31/2019 |
| Joe | 10/22/2019 | Kate | 8/15/2019 | Katherine | 6/28/2019 |
| Joe | 11/22/2019 | Kate | 10/16/2019 | John | 8/25/2019 |
| John | 8/13/2019 | Kate | 9/31/2019 | Katherine | 9/4/2019 |
| Joe | 8/21/2019 | Aaron | 11/1/2019 | Katherine | 4/3/2019 |
I would like to create a new table that shows the unique users with the latest date the modified something and what they modified (Task, Activity, Process). Here is the end result of the example above:
| User | Last Modified Date | Item Modified |
| Aaron | 11/1/2019 | Activity |
| Joe | 11/22/2019 | Activity |
| John | 11/20/2019 | Activity |
| Kate | 10/16/2019 | Task |
| Katherine | 9/4/2019 | Process |
For example, the first table had Katherine lastly modify a Process on 9/4/2019 so that's why it shows up like that. John shows up in Process and Activity, but the latest date is Activity for 11/20/2019 hence the result.
Any thoughts if this is possible? I can't wrap my head around the formulas ...
Hello Anonymous
My first thought is to split the items into 3 tables, tag them with their type and stack them.
Then you just use the stacked table and a couple measures.
I have attached my sample file for you to look at.
If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
Hi,
Please find attached.
6 Replies
Hi,
Here's a solution without creating 3 Tables. You may download my PBI file from here.
Hope this helps.
Hello Ashish_Mathur
I cannot download the .pbix due to my organization restricting the source. Could you please upload it to the forum like the other responder did above?
Thank you,
Bogdan
Hi,
Please find attached.
Hello Anonymous
My first thought is to split the items into 3 tables, tag them with their type and stack them.
Then you just use the stacked table and a couple measures.
I have attached my sample file for you to look at.
If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
jdbuchanan71 Thank you for the instructions and sharing the .pbix file, this worked perfectly. I have a questions regarding creating the 5 queries to make this- I will probably have over 10,000 rows - will this slow performance/load time/refresh time by a lot?
No, that should be no problem. Also, of the 5 queries (1 original, 3 splits, 1 stack) you only have to load the last one to your data model. In the query editor you can right click on a query and turn off 'Load to model'.