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
afaherty
Helper IV
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!

 

IDDateTestScorePassFail
1238/1/2019Math50Fail
12312/1/2019Math80Pass
1233/1/2020Math100Pass
3459/1/2019English100Pass
34511/1/2019English50Fail
3451/3/2020English40Fail
6784/1/2020Writing55Fail
6785/1/2020Writing84Pass
6786/1/2020Writing60Fail
8905/1/2021Math62Fail
8906/1/2021Math80Pass
9127/1/2021English80Pass
4334/1/2021Writing45Fail
4336/1/2021Writing78Pass
1678/1/2021Math100Pass

 

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

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

15 REPLIES 15
daxer-almighty
Solution Sage
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:

daxeralmighty_0-1631968279195.png

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.

daxer-almighty
Solution Sage
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.

daxer-almighty
Solution Sage
Solution Sage

Hi @afaherty 

 

// 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 =
    ADDCOLUMNS(
        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:

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwi0mrvUuojzA...

 

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

@afaherty 

 

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

Ashish_Mathur
Super User
Super User

Hi,

Please show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

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

afaherty_0-1631834597408.png

Thanks!

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)
 

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
https://www.linkedin.com/in/excelenthusiasts/

Thank you!

Hi @afaherty 

 

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:

VahidDM_0-1631828413254.png

 

 

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

Appreciate your Kudos✌️!!

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

Hi @afaherty 

 

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

IDDateTestScorePassFail
1238/1/2019Math50Fail
12312/1/2019Math80Pass
1233/1/2020Math100Pass
3459/1/2019English100Pass
34511/1/2019English50Fail
3451/3/2020English40Fail
6784/1/2020Writing55Fail
6785/1/2020Writing84Pass
6786/1/2020Writing60Fail
8905/1/2021Math62Fail
8906/1/2021Math80Pass
9127/1/2021English80Pass
4334/1/2021Writing45Fail
4336/1/2021Writing78Pass
1678/1/2021Math100Pass

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

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