Search code examples
powerbicountif

How to use CountIf in PBI to count only certain values that fit the criteria in a column

I am trying to create a table in PBI which only counts data based on a certain condition (i.e. Site Status = Completed OR Ongoing) where there are 3 options for site status (Completed, Ongoing and Terminated).

This is the table that I currently have in my PBI report:

Table

I want this table with the 3rd and 4th column to count sites with a condition

However, in the 3rd column I want a count of sites with the status 'Ongoing' or 'Completed'. I also want a 4th column where we only count the sites that have 'Terminated' but I am assuming the solution for the 3rd column will closely translate to the solution for the 4th column. To clarify I just want the 3rd and 4th column to count the no of sites which are either Ongoing or Completed, and then only terminated (Per country.)

Any help would be amazing, thank you in advance.

Solution

  • Ongoing or Completed. Since you're filtering for a string value / constant, you can just do this (instead of using FILTER):

       CALCULATE( 
        [Site Count],
         OR(
                   'SiteData'[Site Status]="Completed",
                   'SiteData'[Site Status]="Ongoing"
                   )
    )

    For "terminated" you'd use the same pattern, but you don't need the OR

    CALCULATE(
      [Site Count],
      'SiteData'[Site Status]="Terminated"
)