How to use distinctcountnoblank for expression

[Anonymous]

Hi,

I would like to know how to use DISTINCTCOUNTNOBLANK on an expression which performs a rowwise calculation on a column instead of directly on the column itself. I cannot create a calculated column because the data model is fixed.

COUNTAX works well in capturing the expression but cannot identify distinct values, and DISTINCT functions require a column.

I am using CALCULATE(COUNTAX(table, expr), FILTER(table, filter)) currently and would like to count only distinct results from the expression.

[v-xicai]

Hi @Anonymous ,

Based on tested, when use this DISTINCTCOUNTNOBLANK Function in a measure for the expression, it's result seems incorrect. As a workaround, you can create a measure as below.

expr = CALCULATE(DISTINCTCOUNT(Table1[letter]),FILTER(ALL(Table1),Table1[letter]<>BLANK()))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xicai ,

I am still unable to use an expression (measure) with the DISTINCTCOUNT function as it demands a column instead.

Also, I would need to filter on another filter other than the noblanks filter.

Could you advise on how to resolve these two issues as well using a DAX?