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
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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are 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. 

Highlighted
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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)