Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper V
7 years ago
Solved

Help with DAX formula to COUNT() or DISCTINTCOUNT() based on condition

Hello to all,   I have the following DAX formula to count unique values in Column1   = DISTINCTCOUNT(Table1[Column1]) How can I COUNT() values equal to "XYZ" and DISTINCTCOUNT() values different...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

    In the second formula, replace COUNT with COUNTA.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
7 years ago

There can be better way but try

cal1 =
var 1 _v1 = calculate(DISTINCTCOUNT(Table1[Column1]),Table1[Column1] <> "XYZ")

var 1 _v2 = calculate(COUNT(Table1[Column1]),Table1[Column1] = "XYZ")

Return V1+v2
  • cgkas's avatar
    cgkas
    Icon for Helper V rankHelper V
    7 years ago

    amitchandak 

    Hi, thanks for your help.

     

    I've tried in this way following your code:

    = IF(Table1[Column1] <> "XYZ",
calculate(DISTINCTCOUNT(Table1[Column1]),Table1[Column1] <> "XYZ"),
 calculate(COUNT(Table1[Column1]),Table1[Column1] = "XYZ")
)

    But is not working as you suggest nor adding the IF(),  I get numeric values for the part "calculate(DISTINCTCOUNT(...))" but I get error for the part " calculate(COUNT(...))"

     

    This formula is invalid or incomplete: 'Calculation error in measure 'Table1'[Column1]: 
    The function COUNT takes an argument that evaluates to numbers or dates and cannot 
    work with values of type String.'.
    • v-yuta-msft's avatar
      v-yuta-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      cgkas ,

       

      I couldn't reproduce your issue, the calculate column works well on my side. Could you please share your sample data for further test?

       

       

      Community Support Team _ Jimmy Tao

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      In the second formula, replace COUNT with COUNTA.

      • cgkas's avatar
        cgkas
        Icon for Helper V rankHelper V
        7 years ago
        Ashish_Mathur wrote:

        Hi,

        In the second formula, replace COUNT with COUNTA.

        Thanks for the suggestion. That was the issue, COUNTA instead of COUNT.