Forum Discussion
Counting Blanks Distinct To An ID
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.
Responsive Resident
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.