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
brettg
Helper II
Helper II

Max Date per Distinct User which updates with date slider

Hi All,

 

I haven't come across a post which deals with this issue yet, so I thought I would share. 

 

I would like to get a Max date for each Distinct User, and this Max date will update based on a date slider I use.

 

This calculated column for example;

 

CALCULATE(MAX('Table'[TestDate]),FILTER('Table', 'Table'[User]=EARLIER('Table'[User])))

 

This will bring back the users latest test in the table as a whole, but if i change the date slider I want this latest test to update within that date range. 

 

Is this possible?

 

Capture1.JPG

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

it does return the date per user, at least with my data
Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

8 REPLIES 8
Stachu
Community Champion
Community Champion

calculated column will not change based on slicer, you will need a measure for that
for a measure this simple syntax should work

Measure = LASTDATE('Table'[Test Date])

if you don't won't to see dates in the total row then use this

Measure no totals = 
VAR User = SELECTEDVALUE('Table'[User])
RETURN
CALCULATE(LASTDATE('Table'[Test Date]),'Table'[User]=User


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thanks for your response!

 

I'm using the second measure you sent across, but it seems to only provide the latest date per row instead of distinct user?

 

Also, I think I need to use Max('Table' [Test Date]) to capture the latest time of the test as well?

 

 

Stachu
Community Champion
Community Champion

if you see evaluation by row then I assume you added this syntax as a column rather than measure, and then it won't work properly.

try adding this code as a measure, then you should only be able to see it in visual & field list, not as a column in the underlying table

EDIT - as for the LASTDATE vs MAX, both should do the job, but indeed MAX may be better choice as it doesn't do context transition



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Capture111.PNG

 

 

I'm hoping this image helps, I have added the syntax as a measure, but I'm trying to show it as a column in a table. 

 

I would just like this max date measure to update when I change the slider, and to be distinct for each user.

 

Will this measure not work properly as a column in my table then?

 

Stachu
Community Champion
Community Champion

it's clear now, I didnt expect you wanted to show dates in rows as well

try this syntax

Measure no totals = 
VAR User = SELECTEDVALUE('Table'[User])
RETURN
CALCULATE(MAX('Table'[Test Date]),ALLSELECTED('Table'[Test Date]),'Table'[User]=User)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

 

Thank you for your help with this, it is appreciated!

 

This measure is only giving a max date per row by the looks of it. Is there anyway to add a 'Group by' to this measure, so I can get one max date per distinct user?

 

 

Stachu
Community Champion
Community Champion

it does return the date per user, at least with my data
Capture.PNG



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

 

 

I see! If you just have the User and the Measure in a table, it does one max date per distinct user.

 

Can I use this Latest test measure in other visuals?

 

For example; 

 

If I want to show the results of the tests in a pie chart, but this pie chart only shows the latest test of each user. How would I include this latest test measure in the filters?

 

Thanks for your help

 

 

Capture23322.PNG

 

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.