Power Pivot Principles: The A to Z of DAX Functions – DISTINCT(table)

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DISTINCT (table). 

The DISTINCT (table) function

The DISTINCT(table) function is one of the table manipulations functions that returns a table containing only the distinct rows.  This means that the duplicate rows are removed, and only unique rows are returned.  It has the following syntax:

It has just the one [1] argument:

• table: this is required and represents the table from which unique rows are to be returned or an expression that returns a table.

Here are a few remarks about this function:

• the current filter context has an impact on the results of the DISTINCT(table) function
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules
• there is another version of the DISTINCT(table) function which is the DISTINCT(column) function.  The latter function removes the duplicate value for a column or expression that resulted in column.

For example, we can create a simple table using the DATATABLE function in DAX:

It should return the follow table in Excel:

Then we can use the DISTINCT(table) function to wrap around the DAX code to remove duplicate row for this table:

Hence, the code above will result in the following table:

Come back next week 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.