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 4, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/approximate-distinctcount-function-dax