Welcome to our post all about the DISTINCTCOUNT function in DAX, because sometimes a COUNT isn’t good enough because it counts duplicates.

If you want to use the same Power BI file I use in the below video and written notes to try out DISTINCTCOUNT, here’s the pbix file to download. You can also click the graphic below to get it.

Video Instructions:

Written Instructions:

We’re going to use a super simple Power BI file for this… it just has two columns of data. Cities and Values.

I’ve set up a simple table for us so we can see the data, as well as two cards. The first card (where you can see the number 11) has a measure called COUNT added, which counts up the number of cities in our data (duh, right?). If you want to check out the post on the COUNT function, click here.

You also probably notice (mainly because our dataset is small) that while our count of cities is 11, we don’t actually have 11 cities in our data. There’s actually only 6, because some of the cities are in our data multiple times.

We can use the DISTINCTCOUNT function to count UNIQUE values in a column.

Let’s try it out.

Click New measure up in the ribbon, wait for the formula bar to appear about your canvas and enter this code:

Number of Cities = DISTINCTCOUNT('Table'[City])

Nice and easy, right?

How you can drag this new measure to that empty data card that’s on the canvas.

This was a nice and small dataset, but you can use DISTINCTCOUNT on any data, and while we can feasibly count our unique cities visually, you don’t have that option with large datasets.

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.

Contact Joe | TraversData.com | People-Friendly Power BI | LinkedIn