Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!)
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
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:
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."
Hi,
Please show the expected result very clearly.
Hello,
Using the dummy data, here's what I'd hope to produce in PowerBI:
Thanks!
Hi,
You may download my PBI file from here.
Hope this helps.
Thank you! This worked great. My last question is - can you explain the Measure formula that you wrote in words to me?
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.
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:
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:
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 |
Right, but they passed on the first try on 9/1/2019.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
27 | |
26 | |
18 | |
15 | |
14 |