Counting Blank Fields on a Column that's Counting Values

[GON76]

A little background:

I'm trying to count blank fields for a column which is displaying a count of values, the valued that I'm seeing on the card is (blank).

I'm working in a learning environment where I'm trying to count the number of courses that have no enrollments.  The way I'm doing this is to count the value of the column "STUDENTINDEX" for each course, where STUDENTINDEX is a unique number that is assigned to each student - so every student has a STUDENTINDEX.  If a course has no enrollments, it's showing up as a blank field in my table visual. I've tried many approaches to count the number of fields that have no enrollments, and they are all returning (blank) which must mean that the formulas I'm using are not finding any blank fields. So far I've used the following:

• = COUNTBLANK(V_STUDENTS[STUDENTINDEX])
• = COUNTROWS(FILTER(V_STUDENTS, V_STUDENTS[STUDENTINDEX] = 0 || ISBLANK(V_STUDENTS[STUDENTINDEX])))
• = CALCULATE(COUNTROWS(V_STUDENTS),
  FILTER(V_STUDENTS,V_STUDENTS[STUDENTINDEX]=0||V_STUDENTS[STUDENTINDEX]= Blank() ))

I think these formulas are counting the STUDENTINDEX, and returning a blank, because all students have a STUDENTINDEX.  Is there a way that I can just count the blanks on my table visual?

Sorry for the ramble... I'm relatively new to Power BI.

Thanks!

[AmiraBedh]

I assume you have:

V_COURSES table: each row is a course with COURSEID

V_STUDENTS table: each row is a student enrolled in a course with a COURSEID and STUDENTINDEX

There is a relationship from V_COURSES[COURSEID] to V_STUDENTS[COURSEID] (1 to many).

You can create a measure like this:

Courses With No Enrollments =
CALCULATE(
COUNTROWS(V_COURSES),
NOT (V_COURSES[COURSEID] IN VALUES(V_STUDENTS[COURSEID]))
)

Or you can create a calculated column :

Has Enrollments? =
IF(
ISBLANK(CALCULATE(COUNTROWS(V_STUDENTS))),
"No",
"Yes"
)

[muhammad_786_1]

Hi @GON76

Please see if this is what you want.

You can try this measure if you have separate `COURSES` and `V_STUDENTS` tables with a relationship on CourseID:

BlankCount =
COUNTROWS(
FILTER(
COURSES,
ISBLANK(CALCULATE(COUNTROWS(V_STUDENTS)))
)
)

It will count how many courses have no enrollments.

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[Alex_Sawdo]

Building a calulation to find what doesn't exist really depends on how your data model is built. If possible, can you share an image/explain the relationships that exist between your tables? 

Optimally, you'd want to have:

• A courses table
• A students table
• A student-courses table (which students are enrolled in which courses)
  
  With those three tables, you'd write a calculation kind of like this:

Measure = 
CALCULATE(
    COUNTROWS(
        FILTER(
            {{COURSES_TABLE}},
            CALCULATE(
                COUNTROWS(
                    {{STUDENT_ENROLLEMENTS}}
                )
            ) = 0
        )
    )
)​

[GON76]

Sorry, I gave the wrong table name... it's actually V_ENROLLMENTS and it captures all students who are enrolled within a courses via the STUDENTINDEX variable.  Here are a few screenshots.  FYI, I renamed V_ENROLLMENTS[STUDENTINDEX] to just "Enrollments" on my table visual.

Thanks so much for your help!

[AmiraBedh]

Try the following :

Sections With No Enrollments =
CALCULATE(
    COUNTROWS(V_SECTIONCUSTOMFIELDS),
    ISBLANK(CALCULATE(COUNTROWS(V_ENROLLMENTS)))
)

[GON76]

That gave me an error:

[AmiraBedh · Accepted solution]

You are right ! I forgot the FILTER it should be like below :

No Enrollments :=
CALCULATE(
    COUNTROWS(V_SECTIONCUSTOMFIELDS),
    FILTER(
        V_SECTIONCUSTOMFIELDS,
        ISBLANK(
            CALCULATE(COUNTROWS(V_ENROLLMENTS))
        )
    )
)

[GON76]

I ended up using this and it worked!

= CALCULATE( COUNTROWS(V_SECTIONS), FILTER( V_SECTIONS, ISBLANK( CALCULATE(COUNTROWS(V_ENROLLMENTS)) ) ) )

[GON76]

Getting closer!  Now instead of showing Blank the card is showing "1".  Out of curiosity, why did you decide to use "V_SECTIONCUSTOMFIELDS"?  This table houses the custom fields that we created in our learning management system.  They way I'm displaying my visual is to count the STUDENTINDEXs enrolled in each course or in LMS-speak, Section.  Thanks!