Search code examples
powerbidax

DAX Measure to Filter Table for Specific Events on Consecutive Dates

I need to filter a table using a DAX measure based on the following conditions:

For each unique UserID, if that user had an Activity of Run on First Day, an Activity of Run on any number of consecutive dates, and followed by any non-Run Activity and a Hobby of Garden, then keep those rows.

For example, consider the following table:

Date UserID Activity Hobby
01/05/2020 01A Run Stamps
01/06/2020 01A Run Stamps
01/07/2020 01A Walk Garden
01/05/2020 02B Run Stamps
01/06/2020 02B Run Stamps
01/07/2020 02B Run Garden
01/08/2020 02B Run Garden
01/05/2020 03C Run Stamps
01/06/2020 03C Run Stamps
01/07/2020 03C Walk Garden
01/08/2020 03C Walk Garden
01/05/2020 04A Run Stamps
01/06/2020 04B Run Stamps
01/05/2020 04A Run Garden
01/08/2020 04B Run Garden
01/08/2022 04B Run Garden

The first three rows would be kept since UserID 01A ran for two consecutive days then walked and gardened.

The second set of four would be excluded because 02B run four days in a row, but didn't end the streak with a non-running activity.

Of the next set of four rows, only the first three would be kept, since the fourth day's non-running activity was not preceded by two or more days of running, despite gardening on that day.

The final five rows would all be excluded, because users had non-consecutive activities and hobbies that do not meet the criteria. I included them to show the nature of the data in the table, which may have non-consecutive events and multiple events on the same day by a single user.

Solution

  • you can try to create a column.

    Column =
VAR _min =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[UserID] ) )
VAR _max =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[UserID] ) )
VAR _check =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Date] ),
        ALLEXCEPT ( 'Table', 'Table'[UserID] )
    )
        = DATEDIFF ( _min, _max, DAY ) + 1
VAR _walk =
    MINX (
        FILTER (
            'Table',
            'Table'[UserID] = EARLIER ( 'Table'[UserID] )
                && 'Table'[Activity] = "Walk"
        ),
        'Table'[Date]
    )
RETURN
    IF ( _check && NOT ( ISBLANK ( _walk ) ) && 'Table'[Date] <= _walk, 1, 0 )

    enter image description here