cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JimJim Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dax Countx

Try this:

 

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

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Highlighted
Super User
Super User

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 Milwaukee Brew City PUG
Super User
Super User

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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

JimJim Regular Visitor
Regular Visitor

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'

Super User
Super User

Re: Dax Countx

Try this:

 

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

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

JimJim Regular Visitor
Regular Visitor

Re: Dax Countx

Hi @Greg_Deckler, this works, thank you.

 

Thanks to all who contributed. 

Super User
Super User

Re: Dax Countx

Awesome! Glad we got you there!


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,858)