Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.