Reply
Frequent Visitor
Posts: 15
Registered: ‎02-22-2018
Accepted Solution

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.


Accepted Solutions
Highlighted
Super User
Posts: 10,447
Registered: ‎07-11-2015

Re: Dax Countx

Try this:

 

CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post


All Replies
Super User
Posts: 3,484
Registered: ‎06-25-2015

Re: Dax Countx

@JimJim Transform your data type to something other than boolean. Countx can be used with numeric, date and text datatypes.

Near SE WI? Join our PUG MSBIWI
Super User
Posts: 10,447
Registered: ‎07-11-2015

Re: Dax Countx

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 15
Registered: ‎02-22-2018

Re: Dax Countx

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'

Highlighted
Super User
Posts: 10,447
Registered: ‎07-11-2015

Re: Dax Countx

Try this:

 

CompletedQuery = COUNTX(FILTER('TraceData',TraceData[EventClass] = "RPC:Completed" || TraceData[EventClass] = "SQL:BatchCompleted"), TraceData[EventClass])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 15
Registered: ‎02-22-2018

Re: Dax Countx

Hi @Greg_Deckler, this works, thank you.

 

Thanks to all who contributed. 

Super User
Posts: 10,447
Registered: ‎07-11-2015

Re: Dax Countx

Awesome! Glad we got you there!


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!