Sign In
Get Started

Using the DISTINCT Function Effectively in DAX

by | Power BI

Introduction to DAX Functions: DISTINCT

Overview

In this tutorial, we’ll explore the DISTINCT function in Data Analysis Expressions (DAX). DAX is a formula language used for data modeling in Power BI, Power Pivot, and Analysis Services. Understanding and utilizing DAX functions is key to optimizing data analytics. We will start with the DISTINCT function, which returns a one-column table containing the distinct values from the specified column.

Syntax

DISTINCT(Column)
  • Column: The column from which you want to retrieve the unique values.

Practical Implementation

Example Scenario

Consider a table named Sales with the following structure:

Sales Now On Advertisement
TransactionIDProductNameQuantitySalesAmount
1Apple10100
2Banana550
3Apple880
4Orange10120
5Banana550

We want to obtain a list of distinct product names from the Sales table.

DAX Query

EVALUATE
DISTINCT(Sales[ProductName])

Result

ProductName
Apple
Banana
Orange

Integration in Measures and Calculated Columns

Calculated Column

You can use the DISTINCT function within a calculated column to get distinct values and create more complex calculations. Here is an example of creating a calculated column to count the distinct product names in another related table.

DistinctProductCount = COUNTROWS(DISTINCT(Sales[ProductName]))

Measure

For dynamic calculations, you might prefer to use measures:

TotalDistinctProducts = COUNTROWS(DISTINCT(Sales[ProductName]))

This measure can then be used in reports and dashboards to show the total number of distinct products.

Practical Use Case

Scenario: Count the Distinct Customers who Purchased Each Product

  1. Suppose you have another table Customers:
CustomerIDCustomerName
1John Doe
2Jane Smith
3Emily Davis

  1. We have a relationship between the Sales and Customers tables based on CustomerID.


  2. Create a measure to count unique customers for each product:


UniqueCustomersPerProduct = 
COUNTROWS(
  DISTINCT(
    Sales[CustomerID]
  )
)

Visualizing the Result in Power BI

  1. Drag ProductName to the Rows of the Matrix.
  2. Add the measure UniqueCustomersPerProduct to the Values.

This will display the count of distinct customers who purchased each product.

Conclusion

The DISTINCT function in DAX is powerful for filtering unique values from a column, which can be incredibly useful for data analytics. By integrating DISTINCT into calculated columns and measures, you can achieve advanced data insights within Power BI, Power Pivot, or Analysis Services.

Implement these techniques to optimize your data analytics and unleash the full potential of your data models.

Power BI Tools Advertisement

Understanding the DISTINCT Function

Overview

The DISTINCT function in DAX (Data Analysis Expressions) is used to return a one-column table that contains only the distinct values from the specified column. This function is particularly useful for eliminating duplicate entries from data in Power BI, SSAS, or any other data analysis tool that supports DAX.

Syntax

DISTINCT(<column>)

Where:

  • <column> is the column from which you want to retrieve the distinct values.

Implementation

Let’s consider a practical example to solidify the understanding of the DISTINCT function. Suppose we have a table named Sales with the following columns:

  • OrderID
  • CustomerID
  • ProductID
  • Quantity

Example Use Case

We want to get a list of unique CustomerID values from the Sales table.

Step-by-Step Implementation

  1. Table Creation:


    Assume you have the following Sales data:


    OrderID | CustomerID | ProductID | Quantity
    -------------------------------------------
    1       | 101        | P1        | 10
    2       | 102        | P2        | 5
    3       | 101        | P3        | 7
    4       | 103        | P1        | 6
    5       | 102        | P2        | 8

  2. Using DISTINCT Function:


    Apply the DISTINCT function to the CustomerID column. Here is the DAX query:


    EVALUATE 
    DISTINCT(Sales[CustomerID])

  3. Expected Result:


    The result of this query would be a table with unique CustomerID values:


    CustomerID
    -----------
    101
    102
    103

Advanced Use Case

Now, let’s combine DISTINCT with other DAX functions. For example, we want to get the total quantity ordered by each customer but only considering unique orders:

  1. Using DISTINCT in a Measure:


    Create a measure to calculate the total unique quantity ordered by each customer.


    TotalUniqueQuantity = 
    SUMX(
        DISTINCT(Sales[OrderID]),
        Sales[Quantity]
    )

  2. Applying the Measure:


    This measure can then be used in a report, for instance, in a table visual to show CustomerID and TotalUniqueQuantity.


Visualization Example

CustomerID | TotalUniqueQuantity
-----------------------------
101        | 17
102        | 13
103        | 6

For CustomerID 101, the orders (unique by OrderID) have quantities of 10 and 7, totaling to 17.

Conclusion

The DISTINCT function is a powerful tool in DAX for eliminating duplicate entries and retrieving unique values from a column. Its flexibility allows it to be used in combination with other functions to perform more complex data analysis tasks. Applying DISTINCT can help optimize your data models by reducing redundancy and improving clarity in your reports.

Practical Examples of DISTINCT in Action

Example 1: Counting Unique Values in a Column

The DISTINCT function is often used to count the number of unique values in a specified column. Here’s how you can achieve this:

UniqueCustomerCount = COUNTROWS(DISTINCT(Sales[CustomerID]))

Explanation: This measure counts the number of unique customers in the ‘Sales’ table.

Example 2: Creating a Unique List of Values

You can create a new table from another table that contains only the unique values of a specified column using DISTINCT.

UniqueProducts = DISTINCT(Sales[ProductID])

Explanation: This new table, ‘UniqueProducts’, will contain only unique product IDs from the ‘Sales’ table.

Example 3: Using DISTINCT in a CALCULATE Function

DISTINCT can be used in combination with CALCULATE to apply a transformation over a set of unique values.

DistinctProductSales = CALCULATE(SUM(Sales[Amount]), DISTINCT(Sales[ProductID]))

Explanation: This measure calculates the total sales amount over distinct products, eliminating duplicate entries for the same product ID.

Example 4: Filtering with DISTINCT and RELATED

DISTINCT can be used in conjunction with RELATED to filter based on unique values in related tables.

OrderAmountForUniqueCustomers = CALCULATE(
    SUM(Sales[OrderAmount]),
    DISTINCT(RELATED(Customer[CustomerID]))
)

Explanation: This measure calculates the sum of order amounts, but ensures it only counts each customer once by applying DISTINCT to the related CustomerID.

Data Mentor Advertisement

Example 5: Combining DISTINCT with Other Table Functions

You can also use DISTINCT with other table functions like SUMMARIZE to aggregate data on unique combinations of column values.

ProductCategorySales = SUMMARIZE(
    Sales,
    Sales[ProductCategory],
    "TotalSales", SUM(DISTINCT(Sales[ProductID]))
)

Explanation: This table aggregates the sales amount for each product category, summing the distinct product IDs within each category.

Example 6: Using DISTINCT in Virtual Tables

Sometimes, you might want to apply DISTINCT in virtual tables inside other functions.

TotalUniqueOrders = COUNTROWS(
    DISTINCT(
        VALUES(Sales[OrderID])
    )
)

Explanation: This measure returns the count of unique order IDs in the ‘Sales’ table by creating a distinct set of order IDs as a virtual table.

Example 7: Nested DISTINCT Usage

In more complex scenarios, you can nest DISTINCT functions to handle multiple levels of uniqueness.

UniqueSalesPeoplePerRegion = COUNTROWS(
    DISTINCT(
        SELECTCOLUMNS(
            Sales,
            "Region", Sales[Region],
            "SalesPerson", Sales[SalesPersonID]
        )
    )
)

Explanation: This measure calculates the count of unique salesperson-region pairs by first selecting the distinct columns and then applying the DISTINCT function.

Conclusion

The DISTINCT function in DAX is a powerful tool for ensuring data uniqueness and can be used in a variety of scenarios to optimize and refine data analytics. The examples above provide practical implementations that can be directly applied to real-life data analysis projects, allowing for more accurate and insightful results.

Advanced Usage and Combinations

Utilizing DISTINCT with CALCULATE and FILTER

This section explores advanced combinations of the DISTINCT function in DAX. We’ll look into its synergy with CALCULATE and FILTER functions to create robust data models.

Scenario: Calculating the Total Sales for Unique Products

// Assuming we have a Sales table with columns ProductID, SalesAmount, and Date

TotalSalesForUniqueProducts = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        COUNTROWS(
            FILTER(
                Sales,
                Sales[ProductID] = EARLIER(Sales[ProductID])
            )
        ) = 1
    )
)

Explanation

  1. FILTER(Sales, COUNTROWS(FILTER(Sales, Sales[ProductID] = EARLIER(Sales[ProductID]))) = 1): This part filters the Sales table, returning only rows where the ProductID appears exactly once.
  2. CALCULATE(SUM(Sales[SalesAmount]), …): The CALCULATE function is used to recalculate the sum of SalesAmount but only for the filtered rows where the ProductID is unique.

Aggregating Data for Unique Product Categories

// Assuming we have a Products table linked with Sales and Categories tables with columns CategoryID, ProductID

TotalSalesForUniqueCategories = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Products,
        COUNTROWS(
            FILTER(
                Products,
                Products[CategoryID] = EARLIER(Products[CategoryID])
            )
        ) = 1
    )
)

Explanation

  1. FILTER(Products, COUNTROWS(FILTER(Products, Products[CategoryID] = EARLIER(Products[CategoryID]))) = 1): This part filters the Products table, returning only categories that appear exactly once in the Sales data.
  2. CALCULATE(SUM(Sales[SalesAmount]), …): Recalculates the sum of SalesAmount for the filtered products, effectively summing sales of unique categories.

Combining DISTINCT with SUMMARIZE for Detailed Breakdown

// Assuming we have a Sales table with columns Year, SalesAmount, and ProductID

SummaryTable = 
SUMMARIZE(
    Sales,
    Sales[Year],
    "UniqueProductsCount", COUNTROWS(DISTINCT(Sales[ProductID])),
    "TotalSales", SUM(Sales[SalesAmount])
)

Explanation

  1. SUMMARIZE(Sales, Sales[Year], …): Summarizes data at the yearly level.
  2. “UniqueProductsCount”, COUNTROWS(DISTINCT(Sales[ProductID])): Calculates the count of unique ProductIDs for each year.
  3. “TotalSales”, SUM(Sales[SalesAmount]): Calculates the total sales amount for each year.

Combining DISTINCT with EARLIEST for Complex Conditions

// Assuming a table Inventory with columns ProductID, StockLevel

LowStockUniqueProducts = 
CALCULATE(
    COUNTROWS(
        DISTINCT(Inventory[ProductID])
    ),
    FILTER(
        Inventory,
        Inventory[StockLevel] < 10 &&
        Inventory[StockLevel] > EARLIEST(Inventory[StockLevel])
    )
)

Explanation

  1. FILTER(Inventory, Inventory[StockLevel] < 10 && Inventory[StockLevel] > EARLIEST(Inventory[StockLevel])): Filters products that have a stock level below 10 and higher than the earliest stock level.
  2. COUNTROWS(DISTINCT(Inventory[ProductID])): Counts the number of distinct ProductIDs within the filtered condition.

These advanced combinations ensure that the DISTINCT function can be leveraged to its full potential in sophisticated data analytics scenarios, optimizing insights and driving better decision-making.

Integrating DISTINCT with Other DAX Functions

1. DISTINCT with CALCULATE

To optimize results, you can use DISTINCT within a CALCULATE statement. Let’s say we want to calculate the distinct count of sales for specific products.

TotalDistinctSales := 
CALCULATE (
    COUNTROWS ( DISTINCT ( Sales[ProductID] ) ),
    Sales[Region] = "North America"
)

2. DISTINCT with SUMMARIZE

Using DISTINCT within SUMMARIZE allows us to create a new table with summarized details.

SummaryTable :=
SUMMARIZE (
    Sales,
    Sales[ProductID],
    "Unique_Countries", DISTINCT ( Sales[Country] )
)

3. DISTINCT with FILTER

Combine DISTINCT with FILTER to retrieve unique values based on conditions.

UniqueHighValueCustomers :=
DISTINCT (
    FILTER (
        Sales,
        Sales[TotalSaleAmount] > 50000
    )
)

4. DISTINCT with ADDCOLUMNS

Add new calculated columns to the result set that consider only distinct values.

CustomersWithHighPurchases :=
ADDCOLUMNS (
    DISTINCT ( Customers[CustomerID] ),
    "HighValueOrders", 
    CALCULATE (
        COUNTROWS ( Sales ),
        Sales[CustomerID] = Customers[CustomerID],
        Sales[TotalSaleAmount] > 10000
    )
)

5. DISTINCT with ALL

To ignore filters and find distinct values in the entire dataset, use DISTINCT with ALL.

DistinctCategories :=
DISTINCT (
    ALL ( Products[Category] )
)

6. DISTINCT with VALUES

VALUES used with DISTINCT returns unique values from a column, considering the current context.

UniqueRegions :=
DISTINCT ( VALUES ( Sales[Region] ) )

7. DISTINCT with RELATEDTABLE

Combine DISTINCT with RELATEDTABLE to calculate distinct values over related table entries.

DistinctOrderItems := 
CALCULATE (
    COUNTROWS ( DISTINCT ( RELATEDTABLE ( OrderItems ) ) ),
    OrderItems[ProductID] = Products[ProductID]
)

Use these examples to integrate DISTINCT with other DAX functions seamlessly to optimize your data analytics and achieve insightful results.

Related Posts