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

[afaherty]

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

[daxer-almighty]

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.

[daxer-almighty]

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]

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

[afaherty]

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

[daxer-almighty]

@afaherty 

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

[Ashish_Mathur]

Hi,

Please show the expected result very clearly.

[afaherty]

Hello,

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

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[afaherty]

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)

[Ashish_Mathur]

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.

[afaherty]

Thank you!

[VahidDM]

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:

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

Appreciate your Kudos✌️!!

[afaherty]

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

[VahidDM]

Hi @afaherty 

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

[afaherty]

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