COLUMNSTATISTICS - Daxplained

COLUMNSTATISTICS is a DAX function that provides statistics regarding every column in every table in the model.

Syntax

COLUMNSTATISTICS( )

Return Values

A table of statistics. Each row of this table represents a different column in the model. Table columns include:

Table Name: The current column’s table.
Column Name: The current column’s name.
Min: The minimum value found within the current column.
Max: The maximum value found within the current column.
Cardinality: The number of distinct values found within the current column.
Max Length: The length of the longest string found within the current column (only applicable for string columns).

Remarks

Columns in an error state and columns from query-scope calculated tables do not appear in the result table.
If a filter from the filter context is applied to COLUMNSTATISTICS(), an error is returned.
For binary-typed columns, the Min and Max statistics will have BLANK values.

Example

Examples in this article can be used with the sample Adventure Works DW 2020 Power BI Desktop model. To get the model, see DAX sample model.

The following DAX query:

DEFINE
    TABLE FilteredProduct =
        FILTER (
            Product,
            [Color] == "Blue"
        )
    COLUMN Customer[Location] = [State-Province] & " " & [Country-Region]

EVALUATE
COLUMNSTATISTICS ()

Returns a table with statistics regarding all columns from all tables in the model. The table also includes statistics for the query-scope calculated column, Customer[Location]. However, the table does not include the columns from the query-scope calculated table, FilteredProduct.

COLUMNSTATISTICS result table

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/columnstatistics-function-dax