Forum Discussion
Apply filter on measure
I have this data:
studnr course result
| 101 | A | 3,7 |
| 101 | A | 7 |
| 102 | A | 4,6 |
| 103 | A | 6,6 |
| 105 | A | 9,1 |
I want to know to average attemps it took for a student to get a sufficient (5,5 or above) for the course. So this will be 4/3 (4 attempts, 3 persons) = 1,33: Studnr 102 does not count because he did not get a sufficient, 101 took two times and the other ones 1 time.
I already calculated the average attemps taken for this course, which is 1,25 with this measure:
Sure, it goes something like this.
- Generates the list of unique Student / Course
- Filters that list to show only the lines where the student had at least one score > 5.5
- Generates a filtered list of the course table filtered to show just the Student / Course matches from step 2. We count this in one step but because we need the list from step 2 as a list we have to count the list from step 2 later.
- Then we divide the count of courses from step 3 by the count of _students (the list from step 2).
If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
Step 2 is what gets you the list of the 3 students that passed course A.
When you say there are studends with scores < 5.5 do you mean in step 3? That is because you want to count all the tries for the students that passed. One of the tries for student 101 in course A is a 3.7 but you want to count it since his 2nd try he passed with a 7
7 Replies
- jdbuchanan71
Super User
Hello Anonymous
I believe this will give you the result you are looking for. It should also calculate correctly if you select multiple classes.
Avg Attempts Passing = VAR _Studets = FILTER ( SUMMARIZE( StudentResults , StudentResults[Course], StudentResults[Student] ), CALCULATE ( MAX ( StudentResults[Score] ) ) > 5.5 ) VAR _Classes = CALCULATE ( COUNTROWS ( StudentResults ), _Studets) RETURN DIVIDE ( _Classes, COUNTROWS(_Studets) )I have attached my sample .pbix file for you to take a look at.
If this solves your issues please mark it as the solution. Kudos 👍 are nice too.
jdbuchanan71 Yeah it is thanks, but I don't understand this at all. Isn't it possible without those VAR things etc? Just one line, so you don't have to count it later?
Maybe use my measure and add a filter on it?
- jdbuchanan71
Anonymous
I don't know of a way to do it without the VARs or other measure feeding into this one.