How to Count Number of Occurrences in Power BI


You can use the following syntax in DAX to calculate the number of occurrences of each value in a column in Power BI:

occurrences = 
COUNTX(
    FILTER( 'my_data', EARLIER('my_data'[Team]) = 'my_data'[Team] ),
    'my_data'[Team]
)

This particular example counts the number of occurrences of each value in the team column of the table named my_data.

The following example shows how to use this syntax in practice.

Example: How to Count Number of Occurrences in Power BI

Suppose we have the following table in Power BI named my_data:

Suppose we would like to add a new column that contains the count of occurrences of each value in the team column.

To do so, click the New column icon:

Then type in the following formula into the formula bar:

occurrences = 
COUNTX(
    FILTER( 'my_data', EARLIER('my_data'[Team]) = 'my_data'[Team] ),
    'my_data'[Team]
)

This will create a new column named occurrences that contains the count of occurrences of each value in the team column:

Power BI count occurrences of each value in column

For example, we can see:

  • The team name Mavs occurs 4 times.
  • The team name Rockets occurs 2 times.
  • The team name Spurs occurs 3 times.

And so on.

Note: You can find the complete documentation for the COUNTX function in DAX here.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI:

A Simple Formula for “If Contains” in Power BI
How to Write an IF Statement in Power BI
How to Write an IF Statement with Multiple Conditions in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *