Remove matches based on certain rules

[Chenz55]

Hi, 😀

Need assistance in adding this rule in my filtered dax query. I'm new to writing dax queries although happy that i'm leanring something new.

I have a data set where the P_READ_DATE column (string in date format) is my key identifier for duplicate values. There can be multiple matches for the same date.

So basically what I need is any matches found for the P_READ_DATE column I need to keep the latest row based on the [ID_DATE] date & remove the duplicates.The [ID_DATE] column is a string example TABC 3017z20220526151759659 where the date would need to be extracted. Example 20220526.

This is to be done across the whole filtered data set for all the P_READ_DATE. If there are no duplicates found for a specific date then we can keep that value in our filtered data set.

Any help is greatly appreciated.

This is my current code for all the filtered results.

EVALUATE
FILTER(
    SELECTCOLUMNS(
        'reads',
         "ID", [ID],
        "C_READ_DATE", [C_READ_DATE],
        "P_READ_DATE", [P_READ_DATE],
        "ID_DATE", [ID_DATE]
    ),
    [ID] = "5689878" &&
    MID([ID_DATE], 6, 4) = "3017" &&
    DATE(
        VALUE(MID([ID_DATE], 11, 4)),   // Year
        VALUE(MID([ID_DATE], 15, 2)),   // Month
        VALUE(MID([ID_DATE], 17, 2))    // Day
    ) >= TODAY() - 740
)

[Deepak_22]

Hi @Chenz55 , To achieve the desired behavior (keeping only the latest row based on the [ID_DATE] for each P_READ_DATE), you can use the SUMMARIZE function along with ARGMAX in DAX to group by the P_READ_DATE and retrieve the latest row based on the ID_DATE. 

Extract the Date from ID_DATE: , we need a new column that represents the date extracted from [ID_DATE]. This will make our calculations cleaner.

ExtractedDate = 
DATE(
     VALUE(MID([ID_DATE], 11, 4)),   // Year
     VALUE(MID([ID_DATE], 15, 2)),   // Month
     VALUE(MID([ID_DATE], 17, 2))    // Day
)

Modify the DAX Query:

EVALUATE
VAR FilteredReads = 
    FILTER(
        SELECTCOLUMNS(
            'reads',
            "ID", [ID],
            "C_READ_DATE", [C_READ_DATE],
            "P_READ_DATE", [P_READ_DATE],
            "ID_DATE", [ID_DATE],
            "ExtractedDate", DATE(
                               VALUE(MID([ID_DATE], 11, 4)),   
                               VALUE(MID([ID_DATE], 15, 2)),   
                               VALUE(MID([ID_DATE], 17, 2))    
                             )
        ),
        [ID] = "5689878" &&
        MID([ID_DATE], 6, 4) = "3017" &&
        [ExtractedDate] >= TODAY() - 740
    )

VAR FinalResult =
    SUMMARIZE(
        FilteredReads,
        [P_READ_DATE],
        "ID", ARGMAX([ID_DATE], [ID]),
        "C_READ_DATE", ARGMAX([ID_DATE], [C_READ_DATE]),
        "ID_DATE", ARGMAX([ID_DATE], [ID_DATE]),
        "ExtractedDate", ARGMAX([ID_DATE], [ExtractedDate])
    )

RETURN
FinalResult

If you find this helpful, please provide a kudo and mark it as an accepted solution.