Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 ByActivity Last Modified OnTask Last Modified ByActivity Last Modified OnProcess Modified ByProcess Modified On
John11/20/2019Aaron9/15/2019John10/31/2019
Joe10/22/2019Kate8/15/2019Katherine6/28/2019
Joe11/22/2019Kate10/16/2019John8/25/2019
John8/13/2019Kate9/31/2019Katherine9/4/2019
Joe8/21/2019Aaron11/1/2019Katherine4/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:

UserLast Modified DateItem Modified
Aaron11/1/2019Activity
Joe11/22/2019Activity
John11/20/2019Activity
Kate10/16/2019Task
Katherine9/4/2019Process

 

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 ...

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

Hello @Anonymous 

My first thought is to split the items into 3 tables, tag them with their type and stack them.

stacked.jpg

 

Then you just use the stacked table and a couple measures.

LastModified.jpg

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.

View solution in original post

Hi,

Please find attached.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Here's a solution without creating 3 Tables.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

Hello @Anonymous 

My first thought is to split the items into 3 tables, tag them with their type and stack them.

stacked.jpg

 

Then you just use the stacked table and a couple measures.

LastModified.jpg

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.

Anonymous
Not applicable

@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'.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.