Hi,
I have survey data many people taking the survey multiple times.
Example below
Each row is a survey response from the date it was taken.
The only way I know which is the pre survey and which is the post survey is by the date taken, so what I’m looking for is a column that tells me if the survey was completed before of after an assigned date.
I’m looking for the answers to be in a calculated column here not a measure.
What do you think?
Ok let’s have a look into this…
While my preference is for measures most of the time, this is an interesting setup.
Try the below and see how you go with this
Max Date =
VAR CurrentName = 'Survey table'[Name]
RETURN
MAXX(
FILTER( ALL( 'Survey table' ), 'Survey table'[Name] = CurrentName ),
'Survey table'[Date Taken] )
From here you can just run a simple IF statement if you need another column to work out which was the most recent survey taken.
Also a key point here…I would rather this be completed in a measure.
It’s a much better way to run almost any calculation here.
Only a small change required for this one
Max Survey Date =
VAR CurrentName = SELECTEDVALUE( 'Survey table'[Name] )
RETURN
MAXX( FILTER( ALL( 'Survey table' ), 'Survey table'[Name] = CurrentName ),
'Survey table'[Date Taken] )
Nearly always use measures especially with calculations that are occurring over your fact table.
Thanks
Sam
Hi there,
Using the measure above, would it be possible to limit the MAXX filter to only past dates/that happened before “today”?
For example, imagine if there were some forecast survey dates in there for some time in the future, but I just wanted my MAXX date to pick up the last instance that has actually occurred.
Can I somehow get the measure to ignore all instances with a date later than today’s date?
Thanks for your time!
Hello @Juju,
Please create a new post with your question, your work in progress .pbix file and a mock up of the solution you are searching for. This post was already marked with a solution
Best regards,
Alex
