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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors