Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
it does return the date per user, at least with my data
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
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?
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
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?
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)
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?
it does return the date per user, at least with my data
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |