Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago
Solved

Count Student Starts per month

Hello

I need to count student starts per month, student passes in the same month and percentage of pass/fail each month  and visualise in a table. 

I have a student table with columns student ID, start date, status (enrolled/left).

An exam table with student ID, examination date and certification status (pass/fail).

The student table is related to the date table (date, month, month-name, year) on date.

The exam table is related to the student table on student ID.

 

The expected output is:

 

Start Date    Students Starts      Student Pass    %Pass

Sept 2024         40                          5                    12.5

Oct 2024           20                          7                    11.6

Nov                   12                         15                   20.8

 

the percetage passed is based on the total active student starts each month

Many Thanks in advance

Need Help
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    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).

5 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    HI 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.

    • Elisa112's avatar
      Elisa112
      Helper V
      1 year ago

      Hi FarhanJeelani 

      When I try to add Student starts to the table visual I get this error:

      Any ideas?

      thank you

      • FarhanJeelani's avatar
        FarhanJeelani
        Super User
        1 year ago

        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).