Dynamic deletion of the unwanted datetime rows through Dax and Power query

[v-alq-msft]

Scenario:
Suppose we would like to delete the datetime log which is not needed within a certain duration under the same person based on the last record dynamically. How shall we achieve this requirement?

Table Used: 

The requirement is to remove the repeated records happened at a specific time for the same person.

Usually we will turn to Dax to realize it.

In DAX

We may create a calculated column as below.

Column =
VAR _lastrecord =
    CALCULATE (
        MAX ( 'Table'[Datetime] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PersonID] = EARLIER ( 'Table'[PersonID] )
                && 'Table'[AccessID]
                    = EARLIER ( 'Table'[AccessID] ) - 1
        )
    )
RETURN
    IF (
        _lastrecord = BLANK (),
        HOUR ( 'Table'[Datetime] ) * 60
            + MINUTE ( 'Table'[Datetime] )
            + SECOND ( 'Table'[Datetime] ) / 60,
        ABS ( 'Table'[Datetime] - _lastrecord ) * 24 * 60
    )

1. We use EARLIER('Table'[AccessID])-1 to get the last value of the datetime of the same person.
2. If the last record is blank(),then we shall convert the time part of the current value into total minutes. Or we should get the minute duration between two datetime.

3. At last, create an if-parameter to realize a dynamic filter among minutes.
4. Here, we need a measure as below:

_Measure =
VAR _lastrecord =
    CALCULATE (
        MAX ( 'Table'[Datetime] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PersonID] = MAX ( 'Table'[PersonID] )
                && 'Table'[AccessID]
                    = MAX ( 'Table'[AccessID] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISFILTERED ( Parameter[Parameter] ) ),
        MAX ( 'Table'[Datetime] ),
        IF (
            _lastrecord = BLANK (),
            MAX ( 'Table'[Datetime] ),
            IF (
                MAX ( 'Table'[Column] ) <= SELECTEDVALUE ( Parameter[Parameter] ),
                BLANK (),
                MAX ( 'Table'[Datetime] )
            )
        )
     )

Or instead, we can create below measure directly if you don't want to create a calculated column:

Measure =
VAR _ID =
    CALCULATE (
        MAX ( 'Table'[AccessID] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PersonID] = MAX ( 'Table'[PersonID] )
                && 'Table'[AccessID] < MAX ( 'Table'[AccessID] )
        )
    )
VAR _previoustime =
    CALCULATE (
        MAX ( 'Table'[Datetime] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[PersonID] = MAX ( 'Table'[PersonID] )
                && 'Table'[AccessID] = _ID
        )
    )
RETURN
IF (
        NOT ( ISFILTERED ( Parameter[Parameter] ) ),
        MAX ( 'Table'[Datetime] ),
        IF (
            _previoustime = BLANK (),
            MAX ( 'Table'[Datetime] ),
            IF (
                ABS ( DATEDIFF ( _previoustime, MAX ( 'Table'[Datetime] ), MINUTE ) )
                    <= SELECTEDVALUE ( Parameter[Parameter] ),
                BLANK (),
                MAX ( 'Table'[Datetime] )
            )
        )
    )

And you will also see the magic filter as below:

In addition, we can also realize the magic dynamic filtering through Power Query.

In Power Query

We can first create a custom column with the following m codes to get the last datetime record with the same person ID.

=let
id = [IDPersona], index = [IDAcceso],
tab = Table.SelectRows(#"Changed Type with Locale",each [IDPersona]=id and [IDAcceso]=index-1)
in
if Table.IsEmpty(tab) then
null
else
Table.Max(tab,"IDAcceso")[FechaHora]
)

Then create a custom column using below M codes to calculate the time difference compared with the last datetime record.

=if
[Custom]=null then Time.Hour([FechaHora])*60+Time.Minute([FechaHora])+Time.Second([FechaHora])/60
else Number.Abs(Duration.TotalMinutes([FechaHora]-[Custom]))

Since we want to remove the records within a certain duration apart from the current record, we need to take the following steps:
1.Find the last record of the same person.

2.Check whether the record is what we want.

3.If the last record is blank, it should return blank(), if not it should return the value of the last record.

4.Check the time differences between the current record and the last record, if the last record is blank(),we should convert the time part of the current record into minutes, using Time.Hour()*60+Time.Minute()+Time.Second()/60,or we should use Duration.TotalMinutes()
(Here we assume that the set duration is minute).

5.Use below query parameter to remove the records dynamically as we want.

Above is how to use Power Query and DAX to filter out the unwanted datetime rows. I hope this article can be of help when you get the similar questions.

Author: Kelly Yang

Reviewer: Ula Huang, Kerry Wang