Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

How to use distinctcountnoblank for expression

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.

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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?