Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
powerquest1234
Helper III
Helper III

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

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:

powerquest1234_4-1669753822911.png

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)

 

powerquest1234_5-1669754043248.png

 

 

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)?

 

powerquest1234_7-1669754096238.png

 

 

1 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

vcgaomsft_0-1669858113114.png

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

View solution in original post

7 REPLIES 7
djurecicK2
Super User
Super User

djurecicK2
Super User
Super User

Hi @powerquest1234 ,

 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!

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.

powerquest1234_0-1669764138633.png

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:

powerquest1234_3-1669765131526.png

 

Into this:

Student 1: 2

Student 5364: 1

Student 86634 2

 

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

 

 

Hi @powerquest1234 ,
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

vcgaomsft_0-1669796371286.png

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

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.


powerquest1234_0-1669815922085.png

 

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

vcgaomsft_0-1669858113114.png

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

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors