Forum Discussion
Identifying Weekly Surveys being completed
Hi All,
I am trying to do a calculation which allows me to view which clients have completed a survey based on a weekly count.
| ClientID | SurveyID | SurveyType | SurveyDate |
| 1 | S1 | a | 1/01/2024 |
| 1 | S2 | a | 8/01/2024 |
| 1 | S3 | b | 15/01/2024 |
| 1 | S4 | a | 29/01/2024 |
| 2 | S5 | b | 1/01/2024 |
| 2 | S6 | a | 15/01/2024 |
| 3 | S7 | a | 1/01/2024 |
| 3 | S8 | a | 8/01/2024 |
| 3 | S9 | a | 15/01/2024 |
| 3 | S10 | a | 29/01/2024 |
| 4 | S11 | a | 1/01/2024 |
| 4 | S12 | a | 15/01/2024 |
| 4 | S13 | a | 29/01/2024 |
| 5 | S14 | a | 1/01/2024 |
| 5 | S15 | a | 1/02/2024 |
| 5 | S16 | a | 8/01/2024 |
| 5 | S17 | a | 15/01/2024 |
| 5 | S18 | a | 29/01/2024 |
This is the data i am working with. I also have a callender table which has an indirect relationship with survey date.
With this, i am wanting a count (with 'thedate' from the callender table as a slicer for the page) to show me how many clients have completed survey type 'a' at least once a weeek. So with the above table looking at the month, it will be 2 clients. client 2, becuase they have completed a survey 'a' each week, and client 5, who has compelted survey 'a' once a week, even with a double surevey being compelted.
However i would like this to also be flexible with the slicer, so if 'thedate' slicer is set to 01/01/2024-12/01/2023, the results will be 3, because it will now include client 1 who has now met the weekly survey 'a' criteria.
This is the DAX i have gotten to so far, however i am struggling with only counting 1 survey a week, where there can be mutliple survey 'a' competed within 1 week.
Thank you.
1 Reply
It looks like you are trying to count the number of clients who have completed at least one survey of type 'a' each week, based on a slicer for the date range. Your current DAX formula is on the right track, but it needs some adjustments to achieve the desired result.
Here's a modified version of your DAX formula:
Number of Clients who have had at least one Survey 'a' a week =
VAR SelectedWeeks =
DATEDIFF(
MIN('Calendar'[TheDate]),
MAX('Calendar'[TheDate]),
WEEK
)
RETURN
CALCULATE(
DISTINCTCOUNTNOBLANK(Table1[ClientId]),
FILTER(
VALUES('Calendar'[TheDate]),
CALCULATE(
COUNTROWS(
FILTER(
Table1,
Table1[SurveyType] = "a" &&
Table1[SurveyDate] >= MIN('Calendar'[TheDate]) &&
Table1[SurveyDate] <= MAX('Calendar'[TheDate])
)
)
) >= SelectedWeeks
)
)This modified formula uses the VALUES('Calendar'[TheDate]) function to iterate over each selected date in the slicer. For each date, it calculates the count of surveys of type 'a' completed on that date for each client. If the count is greater than or equal to the number of weeks in the selected date range, it includes that client in the final count.
Make sure to replace 'Calendar' with the actual name of your calendar table, and adjust column names accordingly if they are different in your actual data model.
Try this modified formula and see if it gives you the desired result. If you encounter any issues or have specific requirements, feel free to provide more details for further assistance.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
