cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrmaier
Helper IV
Helper IV

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 = 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,
countblank.png
2 ACCEPTED SOLUTIONS
jgeddes
Solution Sage
Solution Sage

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

View solution in original post

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
)

View solution in original post

6 REPLIES 6
jrmaier
Helper IV
Helper IV

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

 

jrmaier_0-1665063604698.png

 

jrmaier
Helper IV
Helper IV

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

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
)

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

jrmaier
Helper IV
Helper IV

@jgeddes that worked. Thank you so much!

jgeddes
Solution Sage
Solution Sage

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors