Need a Dax Measure to count the number of students are passed (should pass all the subjects)

[powerbi__2025]

 

Hi Experts,

I'm working on a Power BI report where the requirement is to calculate the total number of students who have passed all their subjects within a selected date range.

A student is considered as "Passed" only if they have passed all subjects—even if one subject is failed, the student should be treated as "Failed".

I have a working DAX measure that gives the correct result when only today's date is selected. However, when a date range is selected using the slicer (e.g., yesterday + today), it does not return the correct count. It seems to only consider part of the range or just one date.

@lbendlin 
Total_Students_Passed :=
CALCULATE(
DISTINCTCOUNT('Sheet1'[Student]),
FILTER(
VALUES('Sheet1'[Student]),
CALCULATE(
COUNTROWS(
FILTER(
'Sheet1',
'Sheet1'[Exam_Result] = "Fail"
)
)
) = 0
)
)

[v-nmadadi-msft]

Hi @powerbi__2025 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @powerbi__2025 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[FreemanZ]

hi @powerbi__2025 ，

not sure if i fully get you. supposing you have a table like this:

Student	Subject	Result	Date
S1	A	pass	12/7/2025
S1	B	pass	12/7/2025
S1	C	pass	12/8/2025
S1	D	pass	12/9/2025
S2	A	pass	12/6/2025
S2	B	fail	12/7/2025
S2	C	pass	12/8/2025
S2	D	pass	12/9/2025
S3	A	pass	12/6/2025
S3	B	pass	12/7/2025
S3	C	pass	12/8/2025
S3	D	pass	12/9/2025
S3	E	fail	12/10/2025

try to plot a measure like this:

StudentPass = 
COUNTROWS(   
    FILTER(
        ADDCOLUMNS(    
            VALUES(data[student]),
            "Result",
            CALCULATE(CONCATENATEX(VALUES(data[result]), data[Result], ", "))
        ),
        [Result] = "pass"   
    )
)

with date range of 12/06-12/09, it returns 2

with data range of 12/06-12/10, it returns 1, only S1 is qualified. 

Please find more in the attached file. 

[powerbi__2025]

@FreemanZ 

It's not taking cumulative count . Example in the month of June there are 5 students with 100% pass it should show 5

[Cookistador]

Hi @powerbi__2025 

In my previous post, I removed a && during the copy/past processs

I tried on some data, and I get what you try to achieve (if I play with filter, if I select a period without failed, it is count as success but I move the slicer, the KPI changes)µ

This is the DAX measure I created

Total Students Pass =

VAR StudentsInContext =

    CALCULATETABLE(

        VALUES(Sheet1[Student]),

        ALLSELECTED(Sheet1[Date])

    )

VAR StudentsPassed =

    CALCULATE(

        DISTINCTCOUNT(Sheet1[Student]),

        FILTER(

            StudentsInContext,

            VAR CurrentStudent = Sheet1[Student]

            VAR HasFailedInPeriod =

                CALCULATE(

                    COUNTROWS(

                        FILTER(

                            ALL(Sheet1),

                            Sheet1[Student] = CurrentStudent &&

                            Sheet1[Result] = "Fail" &&

                            Sheet1[Date] <= MAX(Sheet1[Date]) &&

                            Sheet1[Date] >= MIN(Sheet1[Date])    

                        )

                    ),

                    ALL(Sheet1[Result]),

                    ALL(Sheet1[Student])

                )

            RETURN

                HasFailedInPeriod = 0

        )

    )

RETURN

    StudentsPassed

[Nasif_Azam]

Hey @powerbi__2025 ,

To correctly count the number of students who passed all their exams within a selected date range in Power BI, we need a DAX measure that:

1. Filters the data by the selected date range.

2. Evaluates each student individually.

3. Confirms that all of their exam results within the selected range are "Pass".

However, as you mentioned, this works only for a single date. To account for a date range, we need to make sure that the filter context includes only the rows within the selected date range. We must explicitly filter by date and isolate each student’s result within the selected range.

Updated Working Measure for Date Range

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    'Sheet1'[Result] = "Fail"
                        && 'Sheet1'[Date] >= MIN('Sheet1'[Date])
                        && 'Sheet1'[Date] <= MAX('Sheet1'[Date])
                )
            )
        ) = 0
    )
)

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[powerbi__2025]

@Nasif_Azam , Tried but no luck it is giving me the only passed one of current date, but not taking the specific date range 

[Nasif_Azam]

Thank you for the screenshots and explanation! The issue with your current DAX measure is that it's not effectively limiting the student's results to only those within the selected date range from the slicer. MIN('Sheet1'[Date]) and MAX('Sheet1'[Date]) in your measure do not respect the slicer context properly, especially when the slicer is working on a separate Date table (which is best practice in Power BI).

Correct DAX Measure for Students Who Passed All Subjects in Selected Date Range

You need to:

1. Respect the slicer context (i.e., limit data to the selected date range).

2. Ensure each student has no "Fail" records in that range.

3. Only include students who attempted all subjects within that range (if needed).

Here is the updated and correct DAX measure:

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    'Sheet1'[Result] = "Fail"
                )
            )
        ) = 0
    )
)

But to respect the slicer-based date filtering, wrap the whole thing inside a CALCULATETABLE with a filter on the date range:

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    'Sheet1'[Result] = "Fail"
                )
            )
        ) = 0
    ),
    KEEPFILTERS( -- Ensure date slicer context is respected
        'Sheet1'[Date]
    )
)

If you are using a separate Date table (recommended), then make sure the relationship is active between DateTable[Date] and Sheet1[Date], and modify the measure as:

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    'Sheet1'[Result] = "Fail"
                )
            )
        ) = 0
    ),
    KEEPFILTERS('DateTable'[Date])
)

---

Things to remember

• Double-check your slicer is filtering on a DateTable, not directly on Sheet1[Date].

• Verify relationships are active: DateTable[Date] → Sheet1[Date].

• Use VALUES('Sheet1'[Student]) to iterate over each student separately.

• Optionally: if students must pass a fixed number of subjects (e.g., 5 subjects), we can add logic to enforce minimum count as well.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[powerbi__2025]

I don't have a separate date table; my dataset contains only 5 columns, one of which is the date column.

[powerbi__2025]

the above code is not giving the cumilative count , same output as earlier

[Nasif_Azam]

To solve the issue correctly, you need to build a measure that does three things:

1. Respects the date range selected (even without a Date table).

2. Evaluates each student independently within the selected date range.

3. Only counts a student if they had no "Fail" result within that date range.

Thanks for the clarification!

Since you don’t have a separate Date table and are slicing directly on 'Sheet1'[Date], here's a revised working measure that fully respects the slicer context and counts only those students who passed all subjects within the selected date range:

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    'Sheet1',
                    'Sheet1'[Result] = "Fail"
                )
            )
        ) = 0
    )
)

This alone will not work if you're selecting multiple days because it considers all dates, not just the slicer-filtered context. So the trick is to explicitly apply the slicer's date filter to the inner calculation.

Fully corrected measure that works without a Date table and handles cumulative date ranges:

Total_Students_Passed :=
CALCULATE(
    DISTINCTCOUNT('Sheet1'[Student]),
    FILTER(
        VALUES('Sheet1'[Student]),
        CALCULATE(
            COUNTROWS(
                FILTER(
                    ALL('Sheet1'),
                    'Sheet1'[Result] = "Fail"
                        && 'Sheet1'[Student] = EARLIER('Sheet1'[Student])
                        && 'Sheet1'[Date] IN VALUES('Sheet1'[Date])
                )
            )
        ) = 0
    )
)

Example from Your Screenshot:

If the slicer is set to 12-06-2025, only S1 should be counted.
If the slicer is set to 11-06-2025, S2 is excluded due to 1 fail.
If the slicer includes 10-06 to 12-06-2025, S1 and S3 are both included (so total 2).

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[powerbi__2025]

@Cookistador No luck, showing as "The true/False expression does not specify a column, Each true/false expression usead as a table filter expression must refer to exactly one column.

[Cookistador]

Just to be sure that I understand your need
Can you pass a sample of the data you use in Power BI ?

I made the test with the following example

And everything is working

[powerbi__2025]

[Cookistador]

I'm sorry, I erased a && during the copy/paste process 😄

with this DAX code, it should work

Total Students Pass =

VAR StudentsInContext =

    CALCULATETABLE(

        VALUES(Sheet1[Student]),

        ALLSELECTED(Sheet1[Date])

    )

VAR StudentsPassed =

    CALCULATE(

        DISTINCTCOUNT(Sheet1[Student]),

        FILTER(

            StudentsInContext,

            VAR CurrentStudent = Sheet1[Student]

            VAR HasFailedInPeriod =

                CALCULATE(

                    COUNTROWS(

                        FILTER(

                            ALL(Sheet1),

                            Sheet1[Student] = CurrentStudent &&

                            Sheet1[Result] = "Fail" &&

                            Sheet1[Date] <= MAX(Sheet1[Date]) &&

                            Sheet1[Date] >= MIN(Sheet1[Date])    

                        )

                    ),

                    ALL(Sheet1[Result]),

                    ALL(Sheet1[Student])

                )

            RETURN

                HasFailedInPeriod = 0

        )

    )

RETURN

    StudentsPassed