08-13-2018 01:59 AM
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;
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?
Solved! Go to Solution.
08-13-2018 02:44 AM
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
08-13-2018 03:22 AM
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?
08-13-2018 03:44 AM - edited 08-13-2018 04:00 AM
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
08-13-2018 04:12 AM
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?
08-13-2018 04:36 AM
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)
08-13-2018 04:54 AM
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?
08-13-2018 06:10 AM - edited 08-13-2018 06:13 AM
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?
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