Can Distinct Values be Counted in Power BI with a 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:

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: