How can I count distinct values with a filter in Power BI?

By /

Power BI is a powerful data analysis and visualization tool that allows users to easily filter and manipulate large datasets. One useful feature of Power BI is the ability to count distinct values while applying filters. This means that users can choose specific criteria to filter their data and then count the unique values within that filtered subset. This feature is particularly useful for identifying patterns and trends within a dataset and gaining insights into the data. By using the count distinct with filter function in Power BI, users can efficiently and accurately analyze their data to make informed decisions.

Power BI: Count Distinct Values with Filter

You can use the following syntax in DAX to count the number of distinct values in a column of a table after applying a filter:

Distinct Points = 
CALCULATE (
    DISTINCTCOUNT ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Team] = "C" )
)

This particular example creates a new measure named Distinct Points that counts the distinct number of values in the Points column of the table, filtered for the rows where the Team column is equal to “C.”

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

Example: How to Count Distinct Values with Filter in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Suppose that we would like to count the number of distinct values in the Points column of the table only for the rows where the Team value is equal to C.

To do so, click the Table tools tab along the top ribbon, then click the New measure icon:

Then type in the following formula into the formula bar:

Distinct Points = 
CALCULATE (
    DISTINCTCOUNT ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Team] = "C" )
)

This will create a new measure named Distinct Points that contains the count of distinct values in the Points column of the table only for the rows where the Team value is equal to C:

Power BI distinct count with filter

If we’d like, we can display this value by going to the Report View in Power BI, then by clicking the Card icon under the Visualizations tab, then by dragging the Distinct Points measure under the Fields label:

This will produce the following card that displays the count of distinct values in the Points column of the table for the rows where the Team is equal to C:

We can see that there are 4 distinct values in the Points column for team C.

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

Additional Resources

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

x

Related terms:

  1. Can Distinct Values be Counted in Power BI with a Filter?
  2. How can I count the distinct values in a column using Power BI?
  3. SAS: Use SELECT DISTINCT in PROC SQL what is the procedure to use SELECT DISTINCT in PROC SQL?
  4. Count Distinct Values in PySpark (3 Methods)
  5. How to Count Distinct Values in SAS (With Examples)
  6. **How to count distinct values in a field in MongoDB?**
  7. How can I retrieve distinct values from multiple columns using Power BI?
  8. How to select distinct values from multiple fields on MongoDB ?
  9. How do you use an advanced filter to filter by date in Excel?
  10. Count Specific Characters in Google Sheets How to count specific characters in google sheets?