Apply filter on measure

[Anonymous]

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: 

#attemptscount = DIVIDE(COUNT(StudentResults[result]);DISTINCTCOUNT(StudentResults[studnr]))

[jdbuchanan71 · Accepted solution]

Sure, it goes something like this.

1. Generates the list of unique Student / Course
2. Filters that list to show only the lines where the student had at least one score > 5.5
3. 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.
4. 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.

[jdbuchanan71]

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.

[Anonymous]

@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.

[Anonymous]

@jdbuchanan71 Can you maybe explain it in detail then? 🙂

[jdbuchanan71 · Accepted solution]

Sure, it goes something like this.

1. Generates the list of unique Student / Course
2. Filters that list to show only the lines where the student had at least one score > 5.5
3. 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.
4. 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.

[Anonymous]

@jdbuchanan71 I don't know exactly why step 2 is neccesary? Can you don't use this in the divide? Also in here why are there still students with a result lower than 5,5?

[jdbuchanan71 · Accepted solution]

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