Forum Discussion
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,
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] = ""))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] = ""))ReturnIF(ISBLANK(_calc),0,_calc)
6 Replies
jgeddesSuper User
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 that worked. Thank you so much!
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
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] = ""))ReturnIF(ISBLANK(_calc),0,_calc)jgeddes thanks for the addition to get the zero result. Take care!