Forum Discussion

GON76's avatar
GON76
Icon for Helper III rankHelper III
1 year ago
Solved

Counting Blank Fields on a Column that's Counting Values

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!

 

 

Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

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

9 Replies

  • Alex_Sawdo's avatar
    Alex_Sawdo
    Icon for Resolver II rankResolver II
    1 year ago

    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's avatar
      GON76
      Icon for Helper III rankHelper III
      1 year ago

      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's avatar
        AmiraBedh
        Icon for Super User rankSuper User
        1 year ago

        Try the following :

        Sections With No Enrollments =
CALCULATE(
    COUNTROWS(V_SECTIONCUSTOMFIELDS),
    ISBLANK(CALCULATE(COUNTROWS(V_ENROLLMENTS)))
)
  • muhammad_786_1's avatar
    muhammad_786_1
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    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

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    1 year ago

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