Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I have survey data with multiple people taking the survey mutiple times. Each row is a survey response with a date taken. The only way I know which is the "pre survey" and which is the post survey is by the date taken. I would like to create a "prepost Colunm that read the date and designated pre or post. I have tried a combination of If and Max but I can't seem to get it to work.. Any tips?
Thanks
Solved! Go to Solution.
Thank you- I see the logic in your approach but I am having trouble with the synax for the "Earlier" section. Per your note, I did the following:
MaxDate = CALCULATE(MAX('Community Leadership Assessment (2)'[Date Talken ], FILTER('Community Leadership Assessment (2)','Community Leadership Assessment (2)'[Name]=EARLIER('Community Leadership Assessment (2)'[Name]))))
But i got this error-A single value for column 'Date Talken ' in table 'Community Leadership Assessment (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Any thoughts?
@CheenuSing wrote:Hi @LAndes
Please try the following
1. Create a column called MaxDate
MaxDate =
CALCULATE(MAX(yourtablename[yourDatecolumn]),FILTER(yourtablename[Name]=EARLIER(yourtablename[Name])))
What this does is computes the MaxDate by Name and popluates in all records grouping by Name.
2. Create a column called IsLatest
IsLatest = If([Date]=[MaxDate],"Latest","Older")
3. Now create a report with relevant columns from yourtablename and use IsLatest column as a Visual Level Filter filtered for "Latest".
Check it out.
If it works please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi @CheenuSing
How would you handle this if there was another variable? Can you apply the filter for another category?
For instance, if there were surveys for two different products that could be answered by the same set of respondents. As in the original problem, each respondent could answer the survey multiple times.
Can you determine the most recent date that Responder 1 took the survey for Product A out of multiple submissions to Product A's survey, which is separart from the most recent date that Responder 1 took the survey for Product B out of multiple submissions to Product B's survey?
So I would want to know:
Survey Type:
Product A Responder 1 Most recent date of Product A survey
Product B Responder 1 Most recent date of Product B survey
Thanks in advance for your help!
Hi @Anonymous
Is your query from 7/28 resolved?
If not, here is what you can try - You can create a new column where you can CONCATENATE(ResponderName,Productname) and use it in EARLIER function per CheenuSing's suggestion. Or you can try adding two filters with EARLIER function.
Thank you- I see the logic in your approach but I am having trouble with the synax for the "Earlier" section. Per your note, I did the following:
MaxDate = CALCULATE(MAX('Community Leadership Assessment (2)'[Date Talken ], FILTER('Community Leadership Assessment (2)','Community Leadership Assessment (2)'[Name]=EARLIER('Community Leadership Assessment (2)'[Name]))))
But i got this error-A single value for column 'Date Talken ' in table 'Community Leadership Assessment (2)' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Any thoughts?
@CheenuSing wrote:Hi @LAndes
Please try the following
1. Create a column called MaxDate
MaxDate =
CALCULATE(MAX(yourtablename[yourDatecolumn]),FILTER(yourtablename[Name]=EARLIER(yourtablename[Name])))
What this does is computes the MaxDate by Name and popluates in all records grouping by Name.
2. Create a column called IsLatest
IsLatest = If([Date]=[MaxDate],"Latest","Older")
3. Now create a report with relevant columns from yourtablename and use IsLatest column as a Visual Level Filter filtered for "Latest".
Check it out.
If it works please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
I was getting a circular dependency error on my indicator column. I just combined them into one calculation and it worked.
Using excel, but this helped me get there. Kudos!
What if we want to calculate it dynamicaly, like if the date range is changed max date will now be recalculated.
Hi @LAndes
It has to be a column and not a measure.
Change it to a column and try again.
Cheers
CheenuSing
Thanks everyone. I added in a .Date into the formula and it worked. Most appreciative!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
24 | |
23 |