Forum Discussion

rayinOz's avatar
rayinOz
Helper III
6 years ago
Solved

COUNT and OR statement?

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
  • Anonymous's avatar
    Anonymous
    6 years ago

    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]
        )
    )
)

     

  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    rayinOz You can use this:

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

     Or

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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")))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    rayinOz , try like 

    TotalBooked = CALCULATE(COUNT('Session Report'[Session Attendance]), FILTER('Session Report', 'Session Report'[Session Attendance] in {"Booked" ,"Attended" }))
  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    rayinOz You can use this:

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

     Or

    TotalBooked =
CALCULATE (
    COUNT ( 'Session Report'[Session Attendance] ),
    KEEPFILTERS ( 'Session Report'[Session Attendance] IN { "Booked", "Attended" } )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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]
        )
    )
)

     

    • rayinOz's avatar
      rayinOz
      Helper III
      6 years ago

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