Provide a count of rows associated with value in another column depending on another column

[Anonymous]

Lets say I have a table like below, that has the following fields:
- Student IDs

- Test Attempt IDs

- "Test Attempt Successful," which determines if the student passed "Yes", had partial credit "Partial" or failed "No."

- "Test Attempt Success Date" which has when the student got a "Yes" in the "Test Attempt Successful?" column (if they passed their test)

The data table looks like this:

Lets say we want to count the rows associated with students who have a "Yes" in the Test Attempt successful column, like below:

(for example, Student 1 has three test attempts; Student 5364 has 2 test attempts; Student 86634 has 3 test attempts; We're not counting on studyent 987 because they have not passed their test)

Assuming that the data table might be out of order, what DAX measure would allow me to create a measure such as below's Total count of asttempts by student (only for students who passed)?

[djurecicK2]

@Anonymous @Greg_Deckler 

 Because measure totals are broken in Power BI 🙂

https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

[djurecicK2]

Hi @Anonymous ,

 Try something like this: (adapt to your table and field names)

Total Count of Attempts= CALCULATE(COUNTA('Table'[Test Attempt ID]), FILTER('Table','Table'[Test Attempt Successful]="Yes"))

Please consider accepting as solution if this answers the question- thanks!

[Anonymous]

Thank you for your response @djurecicK2 !

Hmm, this seems to count the number of passed attempts, but it doesn't count all of the attempts.

I was hoping to create a measure that counted all attempts, but only for students who had at least one "Yes" in the Attempts column.

As I think about it more, I think what I really need is a measure that counts the number of redos that each student has. For example, Student 1 passed the test on their third try, so they had to take the test two extra times. Student 5364 had to take the test a second time, so they had to take the test one extra time. So I would want to to turn this:

Into this:

Student 1: 2

Student 5364: 1

Student 86634 2

If a student passed the first time, they not be counted.

[v-cgao-msft]

Hi @Anonymous ,
Assume that you do not need to take the test again after passing it (at most one "YES" per student).Please try:

Total count of attempts by student = 
VAR _student = MAX('Table'[Student])
VAR _table = CALCULATETABLE(VALUES('Table'[Test Attempt Successful]),'Table'[Student]=_student)
VAR _result = IF("Yes" IN _table,COUNTROWS('Table')-1)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Anonymous]

Out of curiosity, why does the total add up to 8? Even though the number of attempts is correct for the student rows, it seems like it's adding up the rows (attempts and the pass) for all of the students who eventually passed. I also notice that if I had another column (such as Test Attempt ID), the count goes to 0 for each student but adds up to 8.

[v-cgao-msft · Accepted solution]

Hi @djurecicK2 ,

In the total row, there is no filter context from the row, and you can make some changes to complement these filters, like.

Total count of attempts by student = 
VAR _student = MAX('Table'[Student])
VAR _table = CALCULATETABLE(VALUES('Table'[Test Attempt Successful]),'Table'[Student]=_student)
VAR _result = SUMX(SUMMARIZE('Table','Table'[Student],"total",IF("Yes" IN _table,COUNTROWS('Table')-1)),[total])
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[djurecicK2]

Hi @v-cgao-msft ,

 Thanks for your post- I am aware of the workarounds, and was making a bit of a joke 😀. The way that Power BI handles measure totals is not how most users expect. Some people are very passionate about this issue.