Forum Discussion
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'
CompletedQuery = COUNTX('TraceData', TraceData[EventClass] IN {"RPC:Completed", "SQL:BatchCompleted"})
Any help would be greatly appreciated.
Try this:
CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])
7 Replies
JimJim Transform your data type to something other than boolean. Countx can be used with numeric, date and text datatypes.
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.
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'
Try this:
CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])
