How can I count the distinct values in a column using Power BI?

By /

To count the distinct values in a column using Power BI, you can use the “Count Rows” function under the “Add Column” tab. This will create a new column with the count of distinct values in the selected column. Alternatively, you can use the “Distinct Count” function under the “Quick Measures” tab, which will automatically generate a measure for the distinct count of values in the selected column. Both of these methods allow for easy and efficient counting of distinct values in a column within Power BI.

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

Distinct Points = DISTINCTCOUNT('my_data'[Points])

This particular example creates a new measure named Distinct Points that counts the distinct number of values in the Points column of the table named my_data.

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

Example: How to Count Distinct Values in Column 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.

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 = DISTINCTCOUNT('my_data'[Points])

This will create a new measure named Distinct Points that contains the count of distinct values in the Points column of the table:

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:

We can see that there are 12 distinct values in the Points column.

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. SAS: Use SELECT DISTINCT in PROC SQL what is the procedure to use SELECT DISTINCT in PROC SQL?
  2. How can I retrieve distinct values from multiple columns using Power BI?
  3. Can Distinct Values be Counted in Power BI with a Filter?
  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 to select distinct values from multiple fields on MongoDB ?
  8. How do I count the unique values in a column in R?
  9. Pandas: How to Fill NaN Values with Values from Another Column?
  10. Count Specific Characters in Google Sheets How to count specific characters in google sheets?