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 IV
Super User IV

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 III
Super User III

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 IV
Super User IV

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!

Highlighted
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 IV
Super User IV

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 IV
Super User IV

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors