I received a question about how to implement the equivalent of a DISTINCTCOUNTX function in DAX.
Well, such a function doesn’t exist in DAX, but imagine you want to write something like:
UniqueInitials := DISTINCTCOUNTX ( Customer, LEFT ( Customer[Last Name], 1 ) & LEFT ( Customer[First Name], 1 ) )
The corresponding measure working in DAX is:
UniqueInitials := COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( Customer, "Initials", LEFT ( Customer[Last Name], 1 ) & LEFT ( Customer[First Name], 1 ) ) ) )
In a more general way, you can obtain the equivalent of
DISTINCTCOUNTX ( <table>, <expression> )
COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( <table>, "col", <expression> ) ) )
However, be aware that performances will not be very good. The table must be materialized in memory in an uncompressed way, so if you have millions of rows to iterate, it’s going to be expensive. A few thousands rows shouldn’t be an issue if the measure is not called too many times.