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

[jrmaier]

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 = COUNTBLANK('86362'[Student Ethnic Group Name])

I was thinking that if the Student ID field was used for the distinct count (3) that would work, instead of the 12 I get for a result with only COUNTBLANK,

[jgeddes · Accepted solution]

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

)

[jrmaier]

@jgeddes I'm using a Card to display the results.

[jrmaier]

@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 · Accepted solution]

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

)

[jrmaier]

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

[jrmaier]

@jgeddes that worked. Thank you so much!

[jgeddes · Accepted solution]

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

)