Forum Discussion
Helper VCount Student Starts per month
Ji Elisa112 ,
Your DAX expression is trying to evaluate a text value ('Jul 2024') as if it were a Boolean (True/False)—likely due to a line such as:
Student[Status] = "Enrolled"being used outside of a filter function like FILTER() or CALCULATE(), or directly in a context where Power BI expects a Boolean expression.
To Fix It:
Please use CALCULATE to apply filters properly. Here's how to rewrite the measure correctly:Measure 1: Student Starts
Student Starts = CALCULATE( DISTINCTCOUNT(Student[StudentID]), FILTER( Student, Student[Status] = "Enrolled" ) )
If you're grouping by 'Date'[MonthYear], make sure Date is the active date table and is properly related to Student[StartDate].You can add KEEPFILTERS('Date'[MonthYear]) if you're seeing issues with slicers not respecting the context.
Optional: Add a helper column for clarity (if needed)
If you're manually matching 'Jul 2024', it's better to create a MonthYear column like this in your Date table:MonthYear = FORMAT('Date'[Date], "MMM YYYY")
Then use that column in your visual's axis or rows.Double-check:
Student[StartDate] must be properly linked to Date[Date].You should not directly compare 'Jul 2024' to fields unless they're of the same type (Text vs Date).
Super UserHI Elisa112 ,
To achieve this in Power BI, you’ll need to create a summary table that pulls data from both the Student and Exam tables and aggregates by Start Month. Based on your description, let me walk you through the setup and the DAX calculations needed.
Assumptions:
Student[StartDate] is linked to Date[Date].
Exam[ExaminationDate] is not related to Date, but we’ll use the StartDate for monthly grouping.
Students are counted as started if they are Enrolled.
Students are counted as passed if they have a matching Exam record with Certification Status = "Pass".
Step-by-step Solution:
1. Create a calculated column for month grouping (optional but helpful for visuals)
In the Date table:
dax
MonthYear = FORMAT('Date'[Date], "MMM YYYY")
2. Measure: Students Starts
dax
Student Starts =
CALCULATE(
DISTINCTCOUNT(Student[StudentID]),
Student[Status] = "Enrolled"
)Or, if you want this by Start Month:
Student Starts =
CALCULATE(
DISTINCTCOUNT(Student[StudentID]),
Student[Status] = "Enrolled",
ALL('Date'),
KEEPFILTERS('Date'[MonthYear])
)
3. Measure: Student Passes
Student Passes =
CALCULATE(
DISTINCTCOUNT(Exam[StudentID]),
Exam[Certification Status] = "Pass",
Student[Status] = "Enrolled",
ALL('Date'),
KEEPFILTERS('Date'[MonthYear])
)
4. Measure: % Pass
% Pass =
DIVIDE(
[Student Passes],
[Student Starts],
0
)
Visual Setup
Create a Table visual with:
Date[MonthYear]
[Student Starts]
[Student Passes]
[ % Pass ] (format this as percentage)
Sort the table by Date[Date] for chronological order if needed.
Please mark this post as solution if it helps you. Appreciate Kudos.
Helper VWhen I try to add Student starts to the table visual I get this error:
Any ideas?
thank you
- FarhanJeelani
Ji Elisa112 ,
Your DAX expression is trying to evaluate a text value ('Jul 2024') as if it were a Boolean (True/False)—likely due to a line such as:
Student[Status] = "Enrolled"being used outside of a filter function like FILTER() or CALCULATE(), or directly in a context where Power BI expects a Boolean expression.
To Fix It:
Please use CALCULATE to apply filters properly. Here's how to rewrite the measure correctly:Measure 1: Student Starts
Student Starts = CALCULATE( DISTINCTCOUNT(Student[StudentID]), FILTER( Student, Student[Status] = "Enrolled" ) )
If you're grouping by 'Date'[MonthYear], make sure Date is the active date table and is properly related to Student[StartDate].You can add KEEPFILTERS('Date'[MonthYear]) if you're seeing issues with slicers not respecting the context.
Optional: Add a helper column for clarity (if needed)
If you're manually matching 'Jul 2024', it's better to create a MonthYear column like this in your Date table:MonthYear = FORMAT('Date'[Date], "MMM YYYY")
Then use that column in your visual's axis or rows.Double-check:
Student[StartDate] must be properly linked to Date[Date].You should not directly compare 'Jul 2024' to fields unless they're of the same type (Text vs Date).
- Elisa112
- Elisa112
thank you for your time, I am now trying to find only students that have passed in that months intake, for example students 20 enrolled in January 2024, but only 15 passed. This will give a more accurate picture of each cohort intakes success rate.
Any suggestions greatly appreciated
thanks