Forum Discussion

JimJim's avatar
JimJim
Responsive Resident
8 years ago
Solved

Dax Countx

Hi Guys, I am struggling with a simple countx statement in dax. The following dax isn't working for me, I am getting error 'The function COUNTX cannot work with values of type boolean'   CompletedQ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Try this:

     

    CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Something looks very strange in your COUNTX syntax. I've never seen an IN clause in a COUNTX statement before. Is your EventClass a boolean value or I imagine that the boolean value is being returned from the IN clause, it is either 1 or 0 I imagine. You might try wrapping it in a VALUE statement or try COUNTAX. COUNTAX works with logical values.

  • JimJim's avatar
    JimJim
    Responsive Resident
    8 years ago

    Hi, thanks for your reply. The EventClass data type is text.

     

    I tried wrapping a value statement arount the column but got error 'Function CONTAINSROW does not support comparing values of type text with values of type number.

     

    I did also try the countax function and although it parsed successfully, the filter didn't work and returned every row.

     

    I am simply trying to count all rows where EventClass is either 'RPC:Completed' or 'SQL:BatchCompleted'

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      Try this:

       

      CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])
      • JimJim's avatar
        JimJim
        Responsive Resident
        8 years ago

        Hi Greg_Deckler, this works, thank you.

         

        Thanks to all who contributed.