Build a distinct-customer measure with clear eligibility rules

A distinct-customer measure needs a population contract: which transactions qualify, which identifier represents a customer and how unknown identities are treated. Counting a customer dimension answers how many customer records are present under its filters; it does not automatically answer how many customers bought in the selected period.

For a buyer measure, count customer keys from qualifying facts and retain the intended date, product and region context.

Define a known Paid buyer

The retail lab contains five known customer records plus an explicit Unknown member with CustomerKey 0. Customer 5 has no transactions. Customer 3 has only a Pending line.

Known Paid buyers are keys 1, 2 and 4: three customers. Key 0 contributes a Paid amount but does not establish a known person or organization. Counting it as an ordinary customer would produce four distinct keys with a different meaning.

Known Paid Customers =
CALCULATE(
    DISTINCTCOUNT(FactSales[CustomerKey]),
    FactSales[Status] = "Paid",
    KEEPFILTERS(FactSales[CustomerKey] <> 0)
)

The status rule intentionally fixes eligibility to Paid. KEEPFILTERS preserves an existing restriction on the fact customer-key column while excluding Unknown. Other relevant dimension filters remain in effect.

Microsoft documents DISTINCTCOUNT, including its blank-value behaviour and nonadditive totals, in the function reference.

Test the observation window

January has two known Paid customers, keys 1 and 2. February also has two, keys 2 and 4. Across both months, the distinct total is three because customer 2 appears in each month.

Adding the monthly counts gives four, which counts customer 2 twice. That sum describes customer-month occurrences, not unique customers across the combined period.

Name each measure according to the intended unit. Customer-months can be useful in a different analysis, but they should not be labelled distinct customers.

Test category overlap

Software has known Paid customers 1 and 2. Training has known Paid customer 1 plus the excluded Unknown member. Support has known Paid customer 4.

The displayed category counts are therefore two, one and one. Their sum is four, while the overall known Paid customer count remains three. Customer 1 buys across Software and Training and should count once at the total.

Do not force the overall total to equal the visible-row sum without changing the measure's definition. The correct distinct total follows the union of qualifying customer keys.

Keep unknown value visible separately

Excluding Unknown from the customer count does not justify excluding its 5,000 paid paise from total invoice value. These measures have different eligibility needs.

Show unknown-identity amount or line count as a quality measure. A single Unknown key may represent one or many unresolved customers, so it cannot support a reliable count of real customers.

If actual blank keys exist, decide how they are mapped or excluded and test them explicitly. A function's default treatment of BLANK is not a customer-identity policy.

Validate identity before counting

Stable keys matter. If the same customer receives two keys after migration, a distinct count can overstate people even though the SQL or DAX is correct. If two customers are merged under one key, it can understate them.

Document the identity scope: account, person, household, organization or subscription. Avoid treating an email address as a universal immutable customer key without a source contract that supports that use.

A unique dimension key proves row uniqueness, not necessarily real-world identity resolution.

Review the fact and dimension contexts

Under the lab's single-direction model, counting DimCustomer directly may include customer 5 even when no qualifying fact exists. A fact-based buyer measure and a dimension-based registered-customer measure serve different purposes.

The measure contracts provide the baseline. SQLite checks verify the three known Paid keys; DAX filter-context behaviour requires Power BI application review.

Exercise: add another Paid line for customer 1 and confirm the distinct buyer count does not rise. Then add a genuinely new customer with one Paid line and verify that it does. Explain how a duplicated identity would evade a simple line-uniqueness check.

NeuraPath's Data Analytics with Generative AI course connects DAX counts with business identity and eligibility. A useful customer measure explains who qualifies and why its total may differ from adding grouped counts.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in DAX total rows: why summing percentages gives the wrong answer.
• Continue with DAX year-to-date versus rolling twelve months.