Counting Blanks Distinct To An ID

[Anonymous]

Hi everyone,

I am working on a timesheet data set and as such every individual has multiple entries.

I am counting blanks in a column and my simple countblanks measure is returning me multiple blanks per invididual as they have entered multiple timesheets.

I am hoping to do a single count of the blank per individual, so for example if five time sheets have been entered by that individual and i have a blank i am counting, it would return 1 as the result, not 5.

[Adamboer · Accepted solution]

To achieve a single count of the blank per individual, you can modify your measure to first group the data by individual and then count the number of distinct individuals with at least one blank timesheet entry. Here's an example DAX formula you can use:

 
Distinct Count of Blank Time Sheets =
DISTINCTCOUNT(
FILTER(
SUMMARIZE('Table', 'Table'[Individual]),
CALCULATE(
COUNTBLANK('Table'[Time Sheet Column]),
ALLEXCEPT('Table', 'Table'[Individual])
) > 0
)
)

In this formula, replace 'Table' with the name of your timesheet table, 'Individual' with the name of the column that identifies each individual, and 'Time Sheet Column' with the name of the column containing the timesheet entries.

The formula first creates a summary table that groups the data by individual, and then calculates the number of distinct individuals with at least one blank timesheet entry. The ALLEXCEPT function is used to remove any filters on the individual column, allowing the count to consider all timesheet entries for each individual.