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!

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.


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!

Highlighted
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
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 253 members 2,772 guests
Please welcome our newest community members: