DAX Function to Remove Duplicates and Concatenate Based on Date Slicers

This document outlines a DAX formula that removes duplicated values from a specific column and concatenates the results based on two date slicers.

Function Overview

The goal is to create a calculated column or measure that:

• Filters data according to two date slicers.
• Removes duplicates from a given column.
• Concatenates the remaining unique values into a single string.

DAX Function Definition

ConcatenatedValues = 
VAR StartDate = MIN('Date'[Date])  // Get the minimum date from the first date slicer
VAR EndDate = MAX('Date'[Date])    // Get the maximum date from the second date slicer
VAR FilteredTable = 
    FILTER(
        'YourTable', 
        'YourTable'[DateColumn] >= StartDate && 'YourTable'[DateColumn] <= EndDate  // Filter data based on the selected date range
    )
VAR UniqueValues = 
    SUMMARIZE(
        FilteredTable, 
        'YourTable'[ColumnToConcatenate]   // Get unique values from the specified column
    )
RETURN
    CONCATENATEX(
        UniqueValues, 
        'YourTable'[ColumnToConcatenate], 
        ", "  // Use ", " as a separator for concatenation
    )

Explanation of Code Components

1. Variable Declaration:

   • StartDate: Captures the minimum date from the date slicer.
   • EndDate: Captures the maximum date from the date slicer.

2. FilteredTable:

   • Uses the FILTER function to select records from 'YourTable' where dates fall between StartDate and EndDate.

3. UniqueValues:

   • SUMMARIZE: This function generates a table of unique values from ColumnToConcatenate in the filtered context.

4. Concatenation:

   • CONCATENATEX: Concatenates the unique values in UniqueValues, separating them with a comma and a space (", ").

Input Validation

• Ensure that 'YourTable' and the specified columns exist in the data model.
• It is essential that the date slicers are configured on the appropriate date field.
• Verify the data types of the columns used match expectations (e.g., date for DateColumn, text for ColumnToConcatenate).

Usage Example

To use this DAX measure, create a new measure in your data model as follows:

1. Open Power BI Desktop.
2. Navigate to the "Model" view.
3. Right-click on the desired table and select "New Measure."
4. Paste the DAX function code replacing 'YourTable', DateColumn, and ColumnToConcatenate with the actual names from your data model.

Create two slicers on your report using the dates from your date table, and add this measure to a card visual or any other visual to see the concatenated results based on the selected date range.

Conclusion

This DAX function allows for flexible data summarization by removing duplicates and concatenating unique values within a defined date range. For further advanced DAX training and techniques, consider exploring additional resources on the Enterprise DNA Platform.