Distinct Count Aggregation for measures allows count-oriented
measures to avoid the double-counting problem. This would most typically
happen in cases such as:
- Flag Type Measures. These
represent on/off states for a property, such as how many employees
have filed their expense statements? or how many trucks are
current on inspections for this year? They count how many occurrences
are true, or in the case of percentages, display the percent of
the entire population that are true. Because the same actual flag
can appear multiple times across other dimensions, when rolling
up on the Time Dimension, if you did not have a distinct count capability,
you would unintentionally double-count the same property.
- Event Measures. These
count events or the presence or absence of people, equipment, and
so on. Because the item or person may be there one week or month,
and then return, or stay at the same place for an entire period
of time, when aggregating at higher levels of time, you would double-count
the same property. An example is hospital stays. A single hospital
stay can cross time boundaries, so when rolling up on the Time dimension
you would only count each stay once for a particular patient. However,
if a patient has another hospital stay within a time frame, then
that is a new event that has to be counted separately from the first
event.
Distinct Count measures use a special dimension that is otherwise
hidden from end users. During measure loads, PMF uses the special
dimension to differentiate the counted values and ensure no double-counting
when summing values that match along that dimension.