What is the Power BI Count function?
The Power BI Count is an aggregation function that counts the total number of non-blank values available in the input column. This function is quite helpful to use, especially when you want to visualize count based on different criteria.
You can use the Count function with various other DAX functions such as Power BI CALCULATE, CALCULATETABLE, SUM, or other functions. It is similar to the COUNT function used in Excel and other BI tools.
Table of contents
Key Takeaways
- The Power BI Count is an aggregation function that counts the total number of non-blank values available in the specified column.
- This function returns a whole number and does not evaluate any Boolean expressions such as TRUE/FALSE
- You can use the Power BI Count function to count rows with filters, count distinct values, count if measure, count rows with condition and count with condition
- Consider some of the limitations of the Power BI Count function before you use it for your reporting purposes
Syntax
The syntax for the Power BI Count function is outlined below:
COUNT(<column>)
It accepts the below parameter that includes:
| Parameter Name | Mandatory/Optional | Description |
|---|---|---|
| Column | Mandatory | It refers to the column containing the values that you want to count |
The Power BI Count function returns a whole number. In the next section, we will see how to use this function with the associated steps involved.
How to use the Power BI Count function?
To use Power BI Count, follow the steps highlighted below:
Step 1: Launch Power BI Desktop in your system.
Step 2: Import the dataset into Power BI using the Get data option and load it into Power BI using the Load button.
Step 3: Navigate to the Modeling tab and click on the New measure option.
You can also create a calculated column involving the Power BI Count function, though measure is more appropriate to use.
Step 4: In the formula bar, write the logic for the measure using the DAX expression. Save the changes by clicking on the Enter or Commit icon on the Power BI screen.
Step 5: Navigate to the Visualizations pane and choose a visual icon. Map the newly created measure to the visual to create a report in the report canvas.
Note: You can create different measures with Power BI count. For example, to create a Power BI count if measure, you can use the below syntax.
CountIfDropoutRatioLessthan0.5 = COUNTROWS(FILTER(StudentData, StudentData [DropoutRatio] < 0.5))
There is no COUNTIF in-built function available in Power BI. However, using the above syntax you can implement the logic in Power BI.
Similarly, you can implement Power BI count rows with condition and Power BI count with condition.
To implement Power BI count rows with condition, you can use the below syntax
CountRowsWithConditionDemo = COUNTROWS(FILTER(StudentsEnrolled, StudentsEnrolled [Subject] = “Physics”))
To implement Power BI count rows with condition, you can use the below syntax.
CountWithCondition = COUNTX(FILTER(StudentsEnrolled, StudentsEnrolled [Subject] = “Physics”), StudentsEnrolled [NumberOfStudentsEnrolled])
Here we have used COUNTX to evaluate your filter expression and perform counting on the filtered dataset.
Examples
In this section, we will go through a few examples demonstrating the use of the Count function in Power BI.
Example #1 – Using Count Function with Filters in Power BI
In this example, we will demonstrate using the Power BI Count function with Filters in Power BI. To demo this example, we will be using the Spotify_2023 dataset. The Spotify_2023 dataset contains a comprehensive list of the most famous songs of 2023 as listed on Spotify and has information such as track name, artist(s) name, release date, Spotify playlists and charts, streaming statistics, Apple Music presence, Deezer presence, Shazam charts, and various audio features.
To use the Power BI Count function with Filters, follow the instructions below:
Step 1: Import the dataset into Power BI using the Get data option and click on the Load button to load it into Power BI.
Step 2:
- Navigate to the Data pane.
- Right-click on the table.
- Select the New measure option from the contextual menu.
Step 3: In the formula bar, write the logic for the measure using the DAX expression with CALCULATE and CONTAINSSTRING.
Here, we are creating two measures for our analysis. They include:
- Total Music released by Taylor Swift that’s included in Spotify 2023 list
- Total Music released by Taylor Swift in the year 2023
–Measure 1
Total Music released by Taylor Swift = CALCULATE(COUNT(‘spotify-2023′[track_name]),CONTAINSSTRING(‘spotify-2023′[artist(s)_name], “Taylor Swift”))
–Measure 2
Total Music released by Taylor Swift in 2023 = CALCULATE(COUNT(‘spotify-2023′[track_name]),CONTAINSSTRING(‘spotify-2023′[artist(s)_name],”Taylor Swift”),’spotify-2023′[released_year] = 2023)
Click on the Commit icon to save the changes. It will create two measures of your dataset, and you can view them either by navigating to the Table view or Data pane.
Step 4: Navigate to the Visualizations pane and choose the card visual icon. Drag and drop the newly created measures to the visual section.
Here, we have created two card visuals in Power BI for our reporting purposes. You will see the card visual in the report canvas, as shown below.
Example #2 – Count Distinct Values in Power BI with Count Function
In this example, we will demo the use of the Count function to count distinct values in Power BI. In this example, we have used the Gas Stations London dataset for the demo purpose. The Gas Stations London dataset contains a comprehensive collection spotlighting over 115 gas stations across London, along with station ratings, contact information, and other essential details.
Follow the instructions outlined below to use All with multiple column parameters:
Step 1: Open Power BI Desktop, navigate to the Home tab, and click on the Get data option. Select the dataset you want to load and then click on the Load button to load the data into Power BI.
Step 2: Navigate to the Modeling tab. Choose the New measure option.
Step 3: On the formula bar prompt, specify the DAX expression using the Count function.
Here, we are creating multiple calculated measures for our reporting requirements. These include:
- Distinct Count of Gas Stations
- Gas Stations with their websites
- Gas Stations without Phone Numbers
–Measure 1
Distinct Count of Gas Stations = DISTINCTCOUNT(gas_stations_london[title])
–Measure 2
Gas Stations with their websites = COUNT(gas_stations_london[website])
–Measure 3
Gas Stations without Phone Numbers = COUNTBLANK(gas_stations_london[phone])
Click on the Commit icon to save the changes. The newly created measures can be viewed by navigating to the Data pane.
Step 4: Create a data visualization by navigating to the Visualizations pane and choosing the card visual icon. Drag and drop the newly created measures to the visual section.
Here, we have created 3 card visuals to display the distinct count of gas stations, gas stations that have their websites, and gas stations that don’t have any contact information.
Once you have created the visual, your final visual looks as shown below:
Example #3
This example is aimed at demonstrating the use of the Count function in Power BI using the New York Airbnb Data 2024 dataset. The New York Airbnb Data 2024 dataset contains the latest listing activity in New York City, New York, as of January 5th, 2024, along with details such as latitude, longitude, room type, price, ratings, bedrooms, beds, and baths, among others.
You can follow the steps highlighted below to use the Power BI Count function:
Step 1: Choose the dataset using the Get data option. Load the dataset into the Power BI data model by clicking on the Load button.
Step 2: Once the dataset is loaded, right-click on the table and select the New measure option.
Step 3: Write the DAX expression in the formula bar, and specify using the Count function to create a calculated measure.
We have created a measure to calculate the percentage distribution of total Airbnb properties based on the room type, as shown below.
% of Total Properties = DIVIDE(CALCULATE(COUNT(new_york_listings_2024[id])),CALCULATE(COUNT(new_york_listings_2024[id]),ALL(new_york_listings_2024[room_type])))
To save the changes, click on the Commit icon. It will create a new measure in the dataset.
Step 4: Navigate to the Visualizations pane and choose the Table visual icon.
From the Data pane, drag and drop the data fields, including the newly created measure, and map them to the report canvas. It will create a Table visual, as shown below.
Important Things to Note
- The Power BI Count function only accepts column parameters and provides output for columns containing the numbers, string, or date values.
- This function returns a blank value when there are no values available in a column.
- The Power BI Count function doesn’t support any Boolean or True/False values. You can use the COUNTA function for such values.
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Frequently Asked Questions (FAQs)
The key differences between the COUNT and COUNTROWS functions in Power BI are highlighted below:
• COUNT
◦ It counts the number of non-blank rows specified in a column.
◦ You must provide a column for this function for evaluation.
◦ This function returns blank when there are no rows.
• COUNTROWS
◦ Power BI count rows counts the number of rows specified in a table or table expressions.
◦ The table parameter is optional. If this is not provided, by default home table is evaluated.
◦ This function returns blank when there are no rows to perform aggregation. However, for rows not matching the specified criteria, a 0 value is returned.
Yes, you can use COUNT in calculated columns. However, it’s important to note that calculated columns are computed during data refresh and cannot be used to aggregate data or provide dynamic calculations based on report filters or slicers. So, while COUNT can be used in calculated columns, its application might be limited compared to its use in measures.
There are multiple ways you can count values based on multiple criteria in Power BI. They include:
• Apply filters to a table before using functions such as COUNT or COUNTROWS.
• Consider using the COUNTROWS function with a calculated column to count the desired rows
• Use functions like FILTER, CALCULATE, ALL, ALLEXCEPT, etc. to apply contextual filters and count based on filtered conditions.
No, the Power BI COUNT function doesn’t count blank values. It counts only non-blank values and any blank values are excluded from the counting purpose. However, to include blank values for your reporting purposes, consider using other aggregated functions such as COUNTROWS, COUNTBLANK, etc.
Recommended Articles
This has been a guide to Power BI Count. Here we explain how to use it in Power BI, with examples, syntax, benefits, and points to remember. You may learn more from the following articles –
Leave a Reply