DAX Function to Remove Duplicates and Concatenate Values Based on Date Slicers

This guide contains a DAX function that removes duplicate values from a specified column while concatenating those values based on two different date slicers.

Function Overview

Purpose

The function aims to aggregate unique values from a specific column while respecting date filters applied through slicers.

Parameters

• Table: The table containing the data.
• UniqueColumn: The column from which duplicates will be removed and values concatenated.
• StartDate: The beginning date filter applied by the user.
• EndDate: The ending date filter applied by the user.

Return Type

• The function returns a concatenated string of unique values.

Exceptions

• Raises an error if the input table or column is invalid.

DAX Code Implementation

ConcatenateUniqueValues =
VAR SelectedValues =
    FILTER(
        Table,
        Table[DateColumn] >= StartDate && Table[DateColumn] <= EndDate
    )
VAR UniqueValues =
    VALUES(SelectedValues[UniqueColumn])
RETURN
    CONCATENATEX(UniqueValues, UniqueValues[UniqueColumn], ", ")

Explanation of the Code

1. Variable Definition:

   • SelectedValues: Filters the specified table to include only rows where the DateColumn falls between StartDate and EndDate.
   • UniqueValues: Extracts unique values from the filtered results.

2. Return Value:

   • The CONCATENATEX function concatenates the unique values, separated by a comma and a space.

Example Usage

Assuming the following scenario:

• You have a table named Sales containing SalesID, Product, SaleDate, and you want to concatenate unique products sold between two dates.

Code to Invoke the Function:

UniqueProductList = 
ConcatenateUniqueValues(
    Sales,
    Sales[Product],
    DATE(2023, 1, 1),  // Start Date
    DATE(2023, 12, 31) // End Date
)

Explanation of the Example

In this example, the function processes the Sales table, retrieving unique product names sold between January 1, 2023, and December 31, 2023. The output will be a single string containing all unique product names, separated by commas.

Conclusion

This function demonstrates effective data aggregation in DAX while maintaining clarity and performance. For more advanced techniques and detailed courses on DAX and data modeling, consider exploring the resources available on the Enterprise DNA Platform.