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.
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
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |