cancel
Showing results 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

Helper IV

## Need # and % of distinct students who passed exam on their FIRST TRY! Help!

Hello All!

New but learning very quickly!  However, this one tripped me up!

I need to get the total and % of distinct students who passed an exam on their FIRST try (which will be broken down by test type in my matrix).  Here is some similar dummy data I threw together.  I would be forever grateful to whomever can help!  Thanks to all!

 ID Date Test Score PassFail 123 8/1/2019 Math 50 Fail 123 12/1/2019 Math 80 Pass 123 3/1/2020 Math 100 Pass 345 9/1/2019 English 100 Pass 345 11/1/2019 English 50 Fail 345 1/3/2020 English 40 Fail 678 4/1/2020 Writing 55 Fail 678 5/1/2020 Writing 84 Pass 678 6/1/2020 Writing 60 Fail 890 5/1/2021 Math 62 Fail 890 6/1/2021 Math 80 Pass 912 7/1/2021 English 80 Pass 433 4/1/2021 Writing 45 Fail 433 6/1/2021 Writing 78 Pass 167 8/1/2021 Math 100 Pass

Maybe @Ashish_Mathur ? (I've noticed you really know your MIN dax functions!)

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
15 REPLIES 15
Solution Sage

Following my 2 posts above, I show you that the measure by @Ashish_Mathur  does not work whereas the one I gave you does:

When you filter by the date, student 1 should not be counted since his very exam in math was a Fail on 8/01/2019. Hence it should be 0. Not 1. There are many other issues with his measure but I don't have time to explain all of them.

Solution Sage

So, I've checked @Ashish_Mathur's measure and I've confirmed that it's not working properly for slices performed on fields like Date, PassFail and Score. The one I gave you above does work the way it should. I'm not saying this to discredit @Ashish_Mathur's measure. I'm just saying that the problem is more complex than it seems at first sight and care should be taken to clearly define the measure so that it makes sense when different slices are taken.

Solution Sage

``````// The question is ill-posed. What you really
// want to calculate is the number of students
// who passed at least one (or all) of the currently
// visible exams. When you select a single exam,
// any of the 2 versions will return the number
// of students that passed the exam on their first
// try. I'm going to implement the first version:
// the number of students that passed at least one
// of the currently visible exams on their first
// try.

// The correct model for which I'm going to write
// the measure has:
// 1. dimension Student with StudentID and [Student Name].
// 2. dimension Test with TestID and [Test Name].
// 3. the fact table (FT) you've shown and the two dims
//    are connected by many-to-one one-way filtering
//    on the obvious fields. The names of the keys
//    in the fact table need to be changed to:
//    StudentId and TestId.
// Obviously, you should only slice data via the dims,
// never by the fact table's fields unless you've got
// a degenerate dimension.

// "1+" - means "passed at least one exam
// on the first try out of those currently
// visible." "All" - would mean "passed all exams
// visible on the first try."
[# Students (1+) ] =
var StudentsWithTestsAndFirstDates =
SUMMARIZE(
FT,
FT[StudentID],
FT[TestID]
),
"@FirstDate",
CALCULATE(
MIN( FT[Date] ),
ALLEXCEPT(
FT,
FT[StudentID],
FT[TestID]
)
)
)
var Result =
CALCULATE(
DISTINCTCOUNT( FT[StudentId] ),
TREATAS(
StudentsWithTestsAndFirstDates,
FT[StudentID],
FT[TestID],
FT[Date]
),
FT[PassFail] = "Pass",
ALL( FT )
)
return
Result``````

I think the measures supplied to you above will not return correct figures if arbitrarily shaped filters come into play. I'll try to prove it. What's more, I'm almost 100% sure the measure given by @Ashish_Mathur will misbehave when you try to slice by, say, any of the Date or Score or PassFail columns (I'll try to prove this as well). Your measure should be immune to such slicing and only consider the students visible and the exams. Nothing more. Otherwise, it won't make much sense in many of the contexts.

By the way, I've written the above measure in such a way that it also works in your one-table model. But you should steer clear of such models due to the disastrous problems they hide. If you want to know what type of problems you might be facing later (without even realizing this), try to google for "auto-exists problems in DAX".

One of the vids that should clearly make you aware what I'm talking about:

Helper IV

Thank you, though I seem to be having some trouble with StudentsWithTestsAndFirstDates.  I receive this error: "The expression refers to multiple columns. Multiple columnscannot be converted to a scalar value."

Solution Sage

I have a pbix file with this measure and get no error.

Super User

Hi,

Please show the expected result very clearly.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

Hello,

Using the dummy data, here's what I'd hope to produce in PowerBI:

Thanks!

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

Thank you! This worked great.  My last question is - can you explain the Measure formula that you wrote in words to me?

Measure = COALESCE(COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[ID]),Data[ID],"ABCD",CALCULATE(MIN(Data[Date]),Data[PassFail]="Pass"),"EFGH",min(Data[Date])),[ABCD]=[EFGH])),0)

Super User

You are welcome.  The SUMMARIZE function creates a virtual table of 3 columns - a unique list of all ID's, the min date of each ID with a pass entry (the title of this column is ABCD) and the min date of each ID (the title of this column is EFGH).  The FILTER function filters the virtual table on the condition of ABCD = EFGH.  Thereafter we count the rows returned by the FILTER function.  The COALESCE function returns a 0 for ID's which do not appear in the filtered table.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

Thank you!

Super User

Try to add a new column for # of students:

``````Pass in 1st Try =
VAR _TNo =
CALCULATE (
COUNT ( 'Table'[ID] ),
ALLEXCEPT ( 'Table', 'Table'[Test], 'Table'[ID] )
)
RETURN
IF ( _TNo = 1, 1, 0 )``````

And try this measure for % of students:

``````% of Pass in 1st Try =
VAR _FP =
CALCULATE (
SUM ( 'Table'[Pass in 1st Try] ),
ALLEXCEPT ( 'Table', 'Table'[Test] )
)
VAR _TT =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
ALLEXCEPT ( 'Table', 'Table'[Test] )
)
RETURN
_FP / _TT``````

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper IV

Hi!  Thank you! I think there's 1 small error though - student # 345 passed the English test on their first try, so the number of students who passed English on the first try would be 2 (#345 and #912).

Super User

Based on data in your table, it seems student #345 done the English test 3 times:

 ID Date Test Score PassFail 123 8/1/2019 Math 50 Fail 123 12/1/2019 Math 80 Pass 123 3/1/2020 Math 100 Pass 345 9/1/2019 English 100 Pass 345 11/1/2019 English 50 Fail 345 1/3/2020 English 40 Fail 678 4/1/2020 Writing 55 Fail 678 5/1/2020 Writing 84 Pass 678 6/1/2020 Writing 60 Fail 890 5/1/2021 Math 62 Fail 890 6/1/2021 Math 80 Pass 912 7/1/2021 English 80 Pass 433 4/1/2021 Writing 45 Fail 433 6/1/2021 Writing 78 Pass 167 8/1/2021 Math 100 Pass
Helper IV

Right, but they passed on the first try on 9/1/2019.

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors