APPROXIMATEDISTINCTCOUNT DAX Function (Aggregation)
Returns an estimated count of the unique values in a column. This function invokes a corresponding aggregation operation in the data source, optimized for query performance but with slightly reduced accuracy. You can use APPROXIMATEDISTINCTCOUNT with the following data sources: Azure SQL, Azure SQL Data Warehouse, BigQuery, Databricks, and Snowflake. Note that this function requires DirectQuery mode. Import mode and dual storage mode are not supported.
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
The SQL query generated uses the APPROX_COUNT_DISTINCT in Transact-SQL to obtain the result.
Examples
-- APPROXIMATEDISTINCTCOUNT can be used only in models using DirectQuery
-- on Azure SQL or Azure SQL Data Warehouse.
-- It uses the faster (but less accurate) version of DISTINCT available
-- on these engines to compute distinct counts: APPROX_COUNT_DISTINCT
-- The function implementation guarantees up to a 2% error rate within
-- a 97% probability.
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]
)
Related functions
Other related functions are:
Last update: Dec 8, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/approximate-distinctcount-function-dax