Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I’ve got the measure below , that counts the number of students based on filtering their Class Type and Exam Status. ([ClassType] = "Audit" &&[ExamStatus] = "Resit") Students can have multiple Class Types and Exam Status, but the measure counts the latest (max) Exam Date.
Evaluate
Row("a", CALCULATE (
COUNTX (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
StudentHistory,
StudentHistory[StudentID],
StudentHistory[ClassType],
"MaxDate", MAX ( StudentHistory[ExamDate] )
),
"ExamStatus", LOOKUPVALUE (
StudentHistory[Exam_Status],
StudentHistory[StudentID], [StudentID],
StudentHistory[ExamDate], [MaxDate]
)
),
StudentHistory[ClassType] = "Audit"
&&[ExamStatus] = "Resit"
), StudentHistory[StudentID] ) ))
For each Class Types and Exam Status they have a tutor that may change , as follows
Even though the numbers returned by the measure is correct, If I add the tutor or the Exam date etc to a report , the Measure number remains the same, but the report surfaces all Students that meet the criteria of ([ClassType] = "Audit" &&[ExamStatus] = "Resit") regardless of it being the latest date or not (the total count is still correct)
My question is, what do I have to do to ensure the only the latest Tutor, Exam Date etc are surfaced in a report? So I don’t for example get 20 students in the report, and the measure is returning a (correct) count of 16
Thanks in advance
Roy
try this measure:
Measure = VAR __AuditOnly = FILTER ( 'StudentHistory', 'StudentHistory'[ClassType] = "Audit" ) VAR __MaxDates = GROUPBY ( __AuditOnly, StudentHistory[StudentID], StudentHistory[ClassType], "ExamDate", MAXX ( CURRENTGROUP (), 'StudentHistory'[ExamDate] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( StudentHistory[StudentID] ), TREATAS ( __MaxDates, StudentHistory[StudentID], StudentHistory[ClassType], StudentHistory[ExamDate] ), 'StudentHistory'[Exam_Status] = "Resit" )
Thanks for the reply,
I should have mentioned in my original post, our tabular cube doesn't support DAX Var?
this is the code without variables
Measure = CALCULATE ( DISTINCTCOUNT ( StudentHistory[StudentID] ), TREATAS ( GROUPBY ( FILTER ( 'StudentHistory', 'StudentHistory'[ClassType] = "Audit" ), StudentHistory[StudentID], StudentHistory[ClassType], "ExamDate", MAXX ( CURRENTGROUP (), 'StudentHistory'[ExamDate] ) ), StudentHistory[StudentID], StudentHistory[ClassType], StudentHistory[ExamDate] ), 'StudentHistory'[Exam_Status] = "Resit" )
Stachu
Thanks for the prompt reply,
I can see the pattern makes sense, but unfortunately our Cube also doesn’t support
TREATAS, GROUPBY or CURRENTGROUP … it’s really old
I’ve tried various things including Using LOOKUP to return the tutor in the measure, but this doesn’t work either, I’m stuck as to what to do now, Nested separate measures for each part?
EVALUATE
ROW (
"a", CALCULATE (
COUNTX (
FILTER (
ADDCOLUMNS (
SUMMARIZE (
StudentHistory,
StudentHistory[StudentID],
StudentHistory[ClassType],
"MaxDate", MAX ( StudentHistory[ExamDate] )
),
"ExamStatus", LOOKUPVALUE (
StudentHistory[Exam_Status],
StudentHistory[StudentID], [StudentID],
StudentHistory[ExamDate], [MaxDate]
),
"Tutor", LOOKUPVALUE (
StudentHistory[Tutor],
StudentHistory[StudentID], [StudentID],
StudentHistory[ExamDate], [MaxDate]
)
),
StudentHistory[ClassType] = "Audit"
&& [ExamStatus] = "Resit"
), StudentHistory[StudentID] )))
the MAX date that you speak of is specific to student id, correct? that is you want to:
1) filter the dates to the latest exam for a given student
2) filter the result to Audit and Resit rows
3) count the student ids
so the measure result based on the screenshot you attached would be 2 for students 100 and 103 as only they have the latest [ExamStatus]="Resit"
do I understand the requirement correctly?
EDIT - adjusted the numbers to match the dates order
Thank you for Getting back to me.
Points 1, 2 and 3 are correct.
The issue is my measure is counting the correctly, in this case 2, but when I pull out other data into a pivot, e.g. Exam date, I end up with all 4 Students, becaue they all took Resits at some stage.
All I want is the Exam Date for 2 students
I should have mentioned in my original post, our tabular cube does not support DAX Var?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |