Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the following formula that calculated column for the number of courses completed by students.
WW Completed Courses =
SUMMARIZE(
'WW Registrations',
[Student ID],
[Academic Year],
[Academic Year End Academic Career],
[Academic Year End Academic Plan No Roll-up Description],
[Gender Description],
[Age Groups],
[Age],
[Ethnicity (IPEDS)],
"Completed Courses", COUNTA('WW Registrations'[Student ID]))
However, I now want to update the measure to exclude courses where the 'Course Grade Official' value is I, IP, N, T, W or (blank). I wasn't sure what the best way to include this into the measure (i.e. as an IF statement or as a filter). I tried using the "NOT" function in the formula, but received a syntax error. I hadn't found many examples of how to list multiple values within a NOT function.
Any assistance would be greatly appreciated! Thanks.
Solved! Go to Solution.
Hi @daviss62,
You can direct add filters on table to filter records:
WW Completed Courses = SUMMARIZE ( FILTER ( ALL ( 'WW Registrations' ), NOT ( 'WW Registrations'[Course Grade Official] IN { "I", "IP", "N", "T", "W", BLANK () } ) ), [Student ID], [Academic Year], [Academic Year End Academic Career], [Academic Year End Academic Plan No Roll-up Description], [Gender Description], [Age Groups], [Age], [Ethnicity (IPEDS)], "Completed Courses", COUNTA ( 'WW Registrations'[Student ID] ) )
Regareds,
Xiaoxin Sheng
Hi @daviss62,
You can direct add filters on table to filter records:
WW Completed Courses = SUMMARIZE ( FILTER ( ALL ( 'WW Registrations' ), NOT ( 'WW Registrations'[Course Grade Official] IN { "I", "IP", "N", "T", "W", BLANK () } ) ), [Student ID], [Academic Year], [Academic Year End Academic Career], [Academic Year End Academic Plan No Roll-up Description], [Gender Description], [Age Groups], [Age], [Ethnicity (IPEDS)], "Completed Courses", COUNTA ( 'WW Registrations'[Student ID] ) )
Regareds,
Xiaoxin Sheng
Hi Xiaoxin,
Thanks for your response! When I try using the formula, I get the following error message:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
I'm not quite sure what the fix is. Is this something you can help me with?
Thanks,
Sean
Hi @daviss62,
My formula is for calculated table, please use it as new table.
Regards.
Xiaoxin Sheng
Hi @v-shex-msft
I actually realized that yesterday afternoon. I must have accidentally clicked "New Measure" instead of "New Table".
Thanks again for your assistance!
Sean
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |