Formula Generator | DAX

DAX Summary of Unique Duplicates per Category

This DAX formula summarizes unique duplicate counts for specific categories within a dataset. Utilizing logical checks, it flags duplicates and aggregates results to facilitate comprehensive analysis and reporting on data quality.

Prompt

will take this dax and give a summary of the unique duplicates per category - DuplicateFlag 2 = 
SWITCH(
    TRUE(),
    'Tracker Data'[Category] = "Vendor Change (Matrix)",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[Spec024] = EARLIER('Tracker Data'[Spec024]) &&
                'Tracker Data'[Required Vendor Code] = EARLIER('Tracker Data'[Required Vendor Code]) &&
                'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "PPM Suspension",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) && 
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Vendor Change (Both)",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]) &&
                'Tracker Data'[Required Vendor Code] = EARLIER('Tracker Data'[Required Vendor Code]) &&
                'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Frequency change (Delete)",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]) &&
                'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Reinstate PM",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Frequency change (Add)",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "SFG20 Code change (add)",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]) &&
                'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Cancel single WO",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Superseded",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    'Tracker Data'[Category] = "Vendor Change WO",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]) &&
                'Tracker Data'[Required Vendor Code] = EARLIER('Tracker Data'[Required Vendor Code])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),

      'Tracker Data'[Category] = "Due date change",
    IF (
        COUNTROWS (
            FILTER (
                'Tracker Data',
                'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) &&
                'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) &&
                'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order])
            )
        ) > 1,
        "Duplicate",
        "Unique"
    ),
    "Unique"  // Default case if none of the categories match
)

Answer

DAX Formula for Summary of Unique Duplicates per Category

Overview

The provided DAX formula identifies duplicates within specific categories of a dataset. This new DAX formula will summarize the count of unique duplicates per category based on the existing duplicate identification logic.

Formula Construction

The new DAX formula will utilize the existing logic to determine duplicates but will aggregate the results by category.

DAX Formula

SummaryUniqueDuplicates = 
SUMMARIZE(
    ADDCOLUMNS(
        'Tracker Data',
        "DuplicateFlag", 
        SWITCH(
            TRUE(),
            'Tracker Data'[Category] = "Vendor Change (Matrix)", IF(COUNTROWS(FILTER('Tracker Data', 'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) && 'Tracker Data'[Spec024] = EARLIER('Tracker Data'[Spec024]) && 'Tracker Data'[Required Vendor Code] = EARLIER('Tracker Data'[Required Vendor Code]) && 'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency]))) > 1, "Duplicate", "Unique"),
            'Tracker Data'[Category] = "PPM Suspension", IF(COUNTROWS(FILTER('Tracker Data', 'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) && 'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) && 'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]))) > 1, "Duplicate", "Unique"),
            'Tracker Data'[Category] = "Vendor Change (Both)", IF(COUNTROWS(FILTER('Tracker Data', 'Tracker Data'[Category] = EARLIER('Tracker Data'[Category]) && 'Tracker Data'[PM Number] = EARLIER('Tracker Data'[PM Number]) && 'Tracker Data'[Work Order] = EARLIER('Tracker Data'[Work Order]) && 'Tracker Data'[Required Vendor Code] = EARLIER('Tracker Data'[Required Vendor Code]) && 'Tracker Data'[Frequency] = EARLIER('Tracker Data'[Frequency]))) > 1, "Duplicate", "Unique"),
            -- Additional categories can be added here
            "Unique"  // Default case
        )
    ),
    'Tracker Data'[Category],
    "CountOfUnique", COUNTROWS(FILTER('Tracker Data', [DuplicateFlag] = "Duplicate"))
)

Explanation of the Formula

ADDCOLUMNS: This function is used to compute a new column ("DuplicateFlag") which checks for duplicates based on the specified category's logic using SWITCH cases.
SWITCH: This portion of the formula assigns "Duplicate" or "Unique" to each row based on the specific checks for provided categories.
COUNTROWS and FILTER: After assigning the appropriate "DuplicateFlag", this formula counts the number of unique "Duplicate" flags for each category using COUNTROWS alongside FILTER to filter on the condition where duplicates are flagged.
SUMMARIZE: This part of the formula aggregates the data, providing a summary count of duplicates per category, enabling easy interpretation and reporting of the data.

Practical Illustration

Assuming the dataset includes entries with various categories and that duplicates are defined based on specific criteria for each category, applying this formula will yield a summarized table output. For example:

Category	CountOfUnique
Vendor Change (Matrix)	5
PPM Suspension	3
Vendor Change (Both)	4
Frequency change (Add)	2

This output shows that there are 5 unique duplicates identified for "Vendor Change (Matrix)", 3 for "PPM Suspension", and so forth. Users can utilize this summary to focus their data analysis and make informed decisions based on the identified duplicates across categories.

Conclusion

The provided DAX formula successfully summarizes the unique duplicates per category based on initial logic that identifies duplicates within the 'Tracker Data'. For comprehensive learning, users are encouraged to explore courses available on the Enterprise DNA Platform to deepen their understanding of DAX and its functionality further.