topic Re: COUNT and OR statement? in DAX Commands and Tips

COUNT and OR statement?

rayinOz — Thu, 27 Aug 2020 00:52:50 GMT

Hello PowerBI users

I'm wanting to use an OR function... ,not sure how to do it... here's the measure:

TotalBooked = CALCULATE(COUNT('Session Report'[Session Attendance]), FILTER('Session Report', 'Session Report'[Session Attendance]="Booked" OR "Attended"))

so we have a session status... of either Booked or Attended (as well as others, wait-listed, withdrawn, etc)... but I'm wanting to get a total of both booked or attended...

How best to do this?

Many thanks ahead of time.

Kind regards

Ray

Re: COUNT and OR statement?

Greg_Deckler — Thu, 27 Aug 2020 01:53:20 GMT

rayinOz - Either will work:

TotalBooked = CALCULATE(COUNT('Session Report'[Session Attendance]), FILTER('Session Report', 'Session Report'[Session Attendance]="Booked" || 'Session Report'[Session Attendance]="Attended")) TotalBooked = CALCULATE(COUNT('Session Report'[Session Attendance]), FILTER('Session Report', OR('Session Report'[Session Attendance]="Booked",'Session Report'[Session Attendance]="Attended")))

Re: COUNT and OR statement?

amitchandak — Thu, 27 Aug 2020 03:20:35 GMT

rayinOz , try like

TotalBooked = CALCULATE(COUNT('Session Report'[Session Attendance]), FILTER('Session Report', 'Session Report'[Session Attendance] in {"Booked" ,"Attended" }))

Re: COUNT and OR statement?

Anonymous — Thu, 27 Aug 2020 07:47:23 GMT

Hi there. Your formula does not adhere to Best Practices and hence may not be as fast as possible. Here's an optimized version:

TotalBooked = CALCULATE( COUNT('Session Report'[Session Attendance]), KEEPFILTERS( TREATAS( {"Booked", "Attended"}, 'Session Report'[Session Attendance] ) ) )

Re: COUNT and OR statement?

AntrikshSharma — Thu, 27 Aug 2020 09:05:19 GMT

rayinOz You can use this:

TotalBooked = CALCULATE ( COUNT ( 'Session Report'[Session Attendance] ), KEEPFILTERS ( 'Session Report'[Session Attendance] = "Booked" || 'Session Report'[Session Attendance] = "Attended" ) )

TotalBooked = CALCULATE ( COUNT ( 'Session Report'[Session Attendance] ), KEEPFILTERS ( 'Session Report'[Session Attendance] IN { "Booked", "Attended" } ) )

Re: COUNT and OR statement?

rayinOz — Fri, 28 Aug 2020 00:55:36 GMT

Hi Daxer! Thanks so much for this, it worked perfectly!