Daily DAX : Day 143 DISTINCT

 In Power BI, the DAX (Data Analysis Expressions) function DISTINCT is used to return a unique list of values from a column or a table expression. It eliminates duplicates, providing a streamlined set of data that can be used for further analysis, filtering, or summarization. It’s a table-returning function, meaning it outputs a single-column table containing distinct values.

Syntax

DISTINCT(<column>) 

OR

DISTINCT(<table>)

    <column>: A reference to a column from which you want to extract unique values.

    <table>: A table expression from which you want to extract unique rows (though typically, it’s used with a single column).

The function returns a table with one column containing only unique (distinct) values from the specified column or unique rows from the specified table.

How It Works

    If applied to a column, DISTINCT scans all the values in that column and removes any duplicates, returning a table with the unique values.

    Blank or null values are treated as a single distinct value (i.e., only one blank will appear in the result if blanks exist).

    It operates within the current filter context, meaning any filters applied to the data model will affect the output.

Main Use Case

The primary use case for DISTINCT is to identify or work with unique values in a dataset, often as part of a larger calculation, summarization, or data exploration process. It’s commonly used in scenarios like:

    Creating Dropdowns or Slicers: Extracting a list of unique items (e.g., product names, customer IDs) for use in report filters.

    Counting Unique Occurrences: Pairing DISTINCT with COUNTROWS to count how many unique values exist in a column.

    Data Validation: Checking for unique entries in a dataset to ensure data quality or consistency.

    Intermediate Steps in Calculations: Generating a unique list as part of a more complex DAX formula.

Examples

Example 1: Extracting Unique Values

Suppose you have a table called Sales with a column Product containing the following values:

```

Product

Phone

Laptop

Phone

Tablet

Laptop

Formula:

UniqueProducts = DISTINCT(Sales[Product])

Result:

UniqueProducts

Phone

Laptop

Tablet

This creates a single-column table with only the unique product names.

#### Example 2: Counting Unique Values

To count the number of unique products:

UniqueProductCount = COUNTROWS(DISTINCT(Sales[Product]))

Result: `3` (because there are three unique products: Phone, Laptop, Tablet).

#### Example 3: Using with Filters

If a filter is applied (e.g., via a slicer selecting only "2023" sales), `DISTINCT` respects that context:

2023Products = DISTINCT(FILTER(Sales, Sales[Year] = 2023))

This returns only unique products sold in 2023.

### Key Notes

- **Comparison to `UNIQUE`**: There’s no direct `UNIQUE` function in DAX; `DISTINCT` serves that purpose. Don’t confuse it with Excel’s `UNIQUE` function.

- **Performance**: For very large datasets, `DISTINCT` can be resource-intensive, so use it judiciously.

- **Difference from `VALUES`**: The `VALUES` function is similar but includes blank values explicitly tied to relationships in the data model, while `DISTINCT` focuses purely on unique values in the column, excluding relationship-driven blanks unless present in the data.

### Practical Use Case Example

Imagine a sales report where you want a slicer to show only unique customer names from a `Customers` table, even if the table has multiple rows per customer due to repeated transactions. You could define a calculated table:

UniqueCustomers = DISTINCT(Customers[CustomerName])

Then use this table as the source for your slicer, ensuring a clean, duplicate-free list.

In summary, `DISTINCT` is a versatile DAX function for isolating unique values, making it invaluable for simplifying data, preparing inputs for visuals, or performing unique counts in Power BI. Let me know if you’d like more examples or clarification!