APPROXIMATEDISTINCTCOUNT DAX Function (Aggregation)
Returns an estimated count of the unique values in a column. The function is optimized for query performance, but the result is slightly less accurate than DISTINCTCOUNT.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| ColumnName |
The column for which the distinct values are counted. |
Return values
The approximate number of distinct values in ColumnName.
Remarks
ColumnName must be a column reference. An expression returns an error.
The function returns BLANK when there are no rows to count.
In DirectQuery mode, the function sends a corresponding aggregation to the data source. The supported data sources are Azure SQL, Azure Synapse dedicated SQL pool, BigQuery, Databricks, and Snowflake. On Azure SQL, the generated SQL query uses APPROX_COUNT_DISTINCT. Direct Lake on SQL also uses this behavior when it falls back to DirectQuery.
In Import mode and in Direct Lake mode without fallback, the engine computes the estimate with a HyperLogLog algorithm. Support for these modes is in preview. Microsoft reports an error rate of about 1.6% for these modes.
Use APPROXIMATEDISTINCTCOUNT for high-cardinality columns when an approximate result is good enough. Use DISTINCTCOUNT when you need an exact result. On low-cardinality columns, APPROXIMATEDISTINCTCOUNT can be slower and use more memory than DISTINCTCOUNT.
Examples
-- APPROXIMATEDISTINCTCOUNT returns an estimate of the number of distinct values.
-- In DirectQuery mode, the data source computes the estimate.
-- In Import and Direct Lake mode (preview), the engine computes it
-- with a HyperLogLog algorithm.
-- For small counts, the estimate can be equal to the exact DISTINCTCOUNT.
DEFINE
MEASURE Sales[#Prods] = DISTINCTCOUNT ( Sales[ProductKey] )
MEASURE Sales[#App Prods] = APPROXIMATEDISTINCTCOUNT ( Sales[ProductKey] )
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
TREATAS ( { "CY 2007", "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),
"#Prods", [#Prods],
"#App Prods", [#App Prods]
)
ORDER BY 'Date'[Calendar Year]
| Calendar Year | #Prods | #App Prods |
|---|---|---|
| CY 2007 | 1258 | 1258 |
| CY 2008 | 1478 | 1478 |
| CY 2009 | 1513 | 1513 |
Related functions
Other related functions are:
Last update: Oct 28, 2026 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/approximate-distinctcount-function-dax