Forum Discussion
Filtering and counting results based on variable date
Hi everyone,
I'm currently looking to create a measure of how many courses users went on to complete on average after completing a specific course (e.g course 2 in the sample data below).
I've gotten stuck with it as the training date for the course varies by user for "course 2" and some users also haven't completed "course 2".
I'm not particularly sure how to describe the question so appologies in advance if it has been asked before!
Table name: TrainingData
| ID Number | Course | Training Date |
| 1 | Course 1 | 10/11/2021 |
| 1 | Course 2 | 01/01/2022 |
| 1 | Course 3 | 05/01/2022 |
| 1 | Course 4 | 06/01/2022 |
| 1 | Course 5 | 06/01/2022 |
| 1 | Course 6 | 06/01/2022 |
| 2 | Course 1 | 03/02/2022 |
| 2 | Course 2 | 01/02/2022 |
| 2 | Course 3 | 01/01/2022 |
| 2 | Course 4 | 05/01/2022 |
| 2 | Course 5 | 06/01/2022 |
| 2 | Course 6 | 03/03/2022 |
| 3 | Course 3 | 05/02/2022 |
| 3 | Course 4 | 06/02/2022 |
| 3 | Course 5 | 07/02/2022 |
| 4 | Course 5 | 03/02/2022 |
| 4 | Course 4 | 04/02/2022 |
| 4 | Course 5 | 05/01/2021 |
| 5 | Course 2 | 03/03/2022 |
| 5 | Course 3 | 01/01/2021 |
| 5 | Course 4 | 04/03/2022 |
| 5 | Course 6 | 05/03/2022 |
Thanks in advance for any help!
Anonymous , Create a measure like
Measure =
var _course = "Course 2" //selectedvalues(Course[Course]) //use independent course table in slicer
var _date = maxx(filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[Course] =_course ), [Date])
return
calculate(countx(values(Table[User]), calculate(count(Table[Course]), filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[Date] > _date))))
2 Replies
Anonymous , Create a measure like
Measure =
var _course = "Course 2" //selectedvalues(Course[Course]) //use independent course table in slicer
var _date = maxx(filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[Course] =_course ), [Date])
return
calculate(countx(values(Table[User]), calculate(count(Table[Course]), filter(allselected(Table), Table[ID] = max(Table[ID]) && Table[Date] > _date))))Hi, Anonymous
Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution.
If you still need help, please share a sample file and expected result here for further research.
Best Regards,
Community Support Team _ Eason
