Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using countblank in one column and get a distinct count of another column values

I created the countblank measure below, but I ultimately would like a distinct count on the number of students who are missing a value in the Student Ethnic Group Name field.   Missing Ethnicity ...
Need Help
  • jgeddes's avatar
    jgeddes
    4 years ago

    You can try the following measure that takes a distinct count of student IDs when the ethnic group name is blank.

     

    Missing Ethnicity =
    CALCULATE(
        DISTINCTCOUNT('86362'[Student ID]),
        OR(ISBLANK('86362'[Student Ethnic Group Name]), '86362'[Student Ethnic Group Name] = "")
    )
  • jgeddes's avatar
    jgeddes
    4 years ago

    Yep.

    Amend the measure to

     

    Missing Ethnicity =
    var _calc =
    CALCULATE(
        DISTINCTCOUNT('86362'[Student ID]),
        OR(ISBLANK('86362'[Student Ethnic Group Name]), '86362'[Student Ethnic Group Name] = "")
    )
    Return
    IF(
        ISBLANK(_calc),
        0,
        _calc
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

jgeddes is it possible to add to the formula to get a "0" (zero) instead of (Blank)? when there are no students missing ethnicity?

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
4 years ago

Yep.

Amend the measure to

 

Missing Ethnicity =
var _calc =
CALCULATE(
    DISTINCTCOUNT('86362'[Student ID]),
    OR(ISBLANK('86362'[Student Ethnic Group Name]), '86362'[Student Ethnic Group Name] = "")
)
Return
IF(
    ISBLANK(_calc),
    0,
    _calc
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    jgeddes thanks for the addition to get the zero result. Take care!