Hi all,
Sorry for the long title! wasnt sure how else to break this down.
So i have 3 tables that have relationships between them as shown below. The third table shows what a tables look like once i put them in a table in a visuliation.
| Table 1 | | | Table2 | | | | Combined Table | |
| ClientID | ClientStart | ClientEnd | SurveyID | DateCompleted | Survey Type | | Client ID | SurveyID | Engagement(months) |
| a | 1/01/2023 | | 1 | 1/01/2023 | y | | a | 1 | 1 |
| b | 1/01/2023 | | 2 | 1/01/2023 | y | | b | 2 | 1 |
| c | 1/01/2023 | | 3 | 1/01/2023 | y | | c | 3 | 1 |
| d | 1/01/2023 | | 4 | 1/01/2023 | n | | d | 4 | 1 |
| e | 1/01/2023 | 1/02/2023 | 5 | 1/01/2023 | y | | e | 5 | 1 |
| f | 1/01/2023 | | 5 | 1/01/2023 | n | | f | 6 | 1 |
| | | | 7 | 1/01/2023 | y | | a | 7 | 1 |
| | | | 8 | 1/01/2023 | y | | a | 8 | 1 |
| | | | 9 | 1/01/2023 | n | | b | 9 | 1 |
| | | | 10 | 1/01/2023 | y | | c | 10 | 1 |
| | | | 11 | 1/01/2023 | y | | d | 11 | 1 |
| | | | 12 | 1/02/2023 | y | | f | 12 | 2 |
| | | | 13 | 1/02/2023 | y | | e | 13 | 2 |
| | | | 14 | 1/02/2023 | n | | e | 14 | 2 |
| | | | 15 | 1/02/2023 | y | | a | 15 | 2 |
| | | | 16 | 1/02/2023 | y | | d | 16 | 2 |
| | | | 17 | 1/02/2023 | y | | e | 17 | 2 |
| | | | 18 | 1/02/2023 | y | | f | 18 | 2 |
| | | | 19 | 1/02/2023 | n | | a | 19 | 2 |
| | | | 20 | 1/02/2023 | y | | c | 20 | 2 |
| | | | 21 | 1/03/2023 | y | | d | 21 | 3 |
| | | | 22 | 1/03/2023 | y | | a | 22 | 3 |
| | | | 23 | 1/03/2023 | y | | b | 23 | 3 |
| | | | 24 | 1/03/2023 | y | | d | 24 | 3 |
| | | | 25 | 1/03/2023 | n | | a | 25 | 3 |
| | | | 26 | 1/03/2023 | y | | a | 26 | 3 |
| | | | 27 | 1/03/2023 | y | | c | 27 | 3 |
| | | | 28 | 1/03/2023 | y | | d | 28 | 3 |
| | | | 29 | 1/03/2023 | y | | f | 29 | 3 |
| | | | 30 | 1/03/2023 | y | | f | 30 | 3 |
What i am trying to get is getting the average number of surveys done by a client ID's for a Card visutaion, one card showing the average between engagement months (Calculated by Datediff of client start and datecompleted) between 1-2months, then 3+ months. I want to exclude all of the 'n' survey Types. I also have the clientend date. This is because if i client does not complete a survey in a month they are still engageed, i would like it to count as a 0 in purposes of the average (such as client 'b' who did not do a survey in the 2nd month of engagement). This is also for the purpose to not include the client if they have ended, for the purpose they do not count towards the average if they have not completed a survey outside of the enddate. The first card will show the average number of surveys completed by a client for the first two months, then the second card will show me the average of surveys completed by a client from 3 months and onward.
an extra criteria i need is I have a filter on the report that filters by datecompleted for other visulations. i need the datecompleted in this card to not be impacted by this filter.
I do not have access to creating new tables or power query (due to how the orgaisation has set up security of data). I need this done through Measures.
Thank you for any support on this request.
