Power Pivot Principles: Introducing the DISTINCT function

Welcome back to the Power Pivot Principles blog.  This week, we will talk about the DISTINCT function in DAX.

The DISTINCT function returns a one-column table that contains the distinct values from the specified column.  In other words, duplicate values are removed and only unique values are returned.  This function cannot be used to return values into a cell or column on a worksheet, rather, it is nested within a formula, to get a list of distinct values that can be passed to another function and then counted, summed, or used for other operations.  A while ago, we wrote about the DISTINCTCOUNT function, so you could argue that this is another distinction!

The DISTINCT function follows the syntax:

Let’s look at an example.  Here, we have sales data from 2016 to 2019, for a number of stores, where they track sales by ‘Product Key’ and ‘Customer Key’:

We want to know the number of unique customers each store gains over time.

To do this, we will load the data to the Power Pivot Data Model, by highlighting the whole data, going to the ‘Power Pivot’ tab on the Ribbon and clicking ‘Add to Data Model’.  We rename the tables as Sales.  We also add a Calendar Table, which will later be used in our PivotTable, and name it Calendar.  We will also define the relationships between Sales and Calendar by switching to ‘Diagram View’ in the Home tab and dragging to connect Date between the two tables.

To count the number of unique customers, we will create a measure ‘Unique Customer’ and use the DISTINCT function as follows:

In here, we cannot bring the list of values that DISTINCT returns directly into a column: there will be a thousand of them.  We just want to know a whole number!  Therefore, we need to nest the DISTINCT function in the COUNTROWS function.  The measure returns a result of 18,484 unique customers, which makes sense, but is not the end result that we want…

Going back to Excel, we create a PivotTable in a new worksheet. Here, we drag Year from the Calendar table into the Rows field, ‘Store Key’ into the Columns field, and ‘Unique Customer’ into the Values field. Thus, we have the PivotTable showing the number of unique customers by stores and by years:

Just to double check, the Grand Total of Grand Totals is 18,484 customers, which coincides with the number generated from our Data Model’s measure!

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.