Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Remove matches based on certain rules

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 col...
General Comment
Need Help
Tips and Tricks
Deepak_22's avatar
Deepak_22
Helper I
2 years ago

Hi Anonymous , 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.