Using DAX to understand if row is part of current streak / sequence

[gavinf4444]

I have a table of records where I've created a couple of new columns to determine whether the row is part of a sequence where the [Denied] column equals 1.

ID	Denied	DateTime	Continuous Denied Access	Max Date	Repeated Expired
27150	0	14/07/2022 08:47	0	20/11/2023 09:37	0
27150	1	18/07/2022 13:43	1	20/11/2023 09:37	0
27150	1	19/07/2022 11:13	2	20/11/2023 09:37	0
27150	1	20/07/2022 14:02	3	20/11/2023 09:37	0
27150	1	15/08/2022 13:58	4	20/11/2023 09:37	0
27150	0	09/09/2022 10:00	0	20/11/2023 09:37	0
27150	1	09/09/2022 13:17	1	20/11/2023 09:37	0
27150	1	12/09/2022 10:24	2	20/11/2023 09:37	0
27150	1	13/09/2022 08:39	3	20/11/2023 09:37	0
27150	1	14/09/2022 08:54	4	20/11/2023 09:37	0
27150	1	14/09/2022 13:50	5	20/11/2023 09:37	1

The DAX code for [Continued Denied Access] is -

Continuous Denied Access = 

SWITCH (
    TRUE (),
    [Denied] = 0, 0,
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALL ( Table1 ),
            [Denied] = 0
                && Table1[ID] = EARLIER ( Table1[ID] )
                && Table1[DateTime] < EARLIER ( Table1[DateTime] )
        )
    ) = 0,
        CALCULATE (
            SUM ( [Denied] ),
            FILTER (
                ALL ( Table1 ),
                Table1[ID] = EARLIER ( Table1[ID] )
                    && Table1[DateTime] <= EARLIER ( Table1[DateTime] )
            )
        ),
    CALCULATE (
        SUM ( [Denied] ),
        FILTER (
            ALL ( Table1 ),
            Table1[ID] = EARLIER ( Table1[ID] )
                && Table1[DateTime]
                    > CALCULATE (
                        MAX ( Table1[DateTime] ),
                        FILTER (
                            ALL ( Table1 ),
                            [Denied] = 0
                                && Table1[ID] = EARLIEST ( Table1[ID] )
                                && Table1[DateTime] < EARLIEST ( Table1[DateTime] )
                        )
                    )
                && Table1[DateTime]<= EARLIER ( Table1[DateTime] )
        )
    )
)

and you can see that this works well by restarting the sequence for that ID when [Denied] equals 0.

The "Repeated Expired" column is there to pick up the latest entry for that ID and determine if the "Denied" column is 1 at the same time - 

Repeated Expired = 

IF(
    [Max Date] = Table1[DateTime] 
    && Table1[Continuous Denied Access] > 0
    ,1
    ,0
    )

What I would like to have is a column which will show me all the values which are in the current streak of Denied so in this case, every entry from "09/09/2022 13:17" onwards, including that row.

It's taken me a long while to get to the stage where I can identify the streak but this final step is proving beyond me so any help is appreciated.

[gavinf4444]

Hi, just to confirm that the column worked to show what's in the current streak.  I'm not 100% sure of how it works but all the testing I've done shows that it does.

As for the 2nd (optimised) code, oddly enough this caused a memory error in PBI.

[OwenAuger]

Glad to hear it 🙂

In the In Current Streak code, the idea is to determine the current streak's start/end by applying a set of filters via CALCULATE. The filters include the current row's ID (using ALLEXCEPT to retain ID but clear other filters introduced due to context transition), DateTime, and Repeated Expired.

In the 2nd expression, it appears it needs to be optimised. CALCULATE/ALLEXCEPT may not work well for this calculation, so maybe stick with your existing code for now 🙂

[OwenAuger · Accepted solution]

@gavinf4444  thanks for the clarification!

Here's one way of writing In Current Streak based on the existing columns:

In Current Streak = 
VAR CurrentStreakEnd =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Repeated Expired] = 1
    )
VAR CurrentStreakStart =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[Denied] = 0,
        Table1[DateTime] < CurrentStreakEnd
    )
RETURN
    IF (
        Table1[DateTime] > CurrentStreakStart
            && Table1[DateTime] <= CurrentStreakEnd,
        "Y",
        "N"
    )

Note:

• If there is no instance of Repeated Expired = 1, then CurrentStreakEnd will be BLANK.
• In this case CurrentStreakStart will also be BLANK.
• This will mean that the condition within IF is FALSE, resulting in a value of "N".

Also, here's a possibly simpler version of Continuous Denied Access:

Continuous Denied Access =
VAR CurrentDateTime = Table1[DateTime]
VAR PreviousZeroDateTime =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[DateTime] <= CurrentDateTime,
        Table1[Denied] = 0
    )
VAR DeniedCount =
    CALCULATE (
        SUM ( Table1[Denied] ),
        ALLEXCEPT ( Table1, Table1[ID] ),
        Table1[DateTime] >= PreviousZeroDateTime,
        Table1[DateTime] <= CurrentDateTime
    )
RETURN
    DeniedCount

Do the above work for you?

Regards

[OwenAuger]

Hi @gavinf4444 

Interesting 🙂

Could you show the values you want to see in the new column for each row of your sample table? Is it a concatenation of DateTime values in the current streak, and is it just for when Repeat Expired = 1 or every row of the streak?

Also, it isn't clear why Repeat Expired = 1 on the last row since Table1[Max Date] ≠ Table1[DateTime] on that row.

Regards

[gavinf4444]

Hi, thanks for the reply.

I've expanded the example below to show that ideally I'd like a column which picks up if that row is part of the current streak where Denied = 1.  

ID	Denied	DateTime	Continuous Denied Access	Max Date	Repeated Expired	In Current Streak
27150	0	14/07/2022 08:47	0	14/09/2022 13:50	0	N
27150	1	18/07/2022 13:43	1	14/09/2022 13:50	0	N
27150	1	19/07/2022 11:13	2	14/09/2022 13:50	0	N
27150	1	20/07/2022 14:02	3	14/09/2022 13:50	0	N
27150	1	15/08/2022 13:58	4	14/09/2022 13:50	0	N
27150	0	09/09/2022 10:00	0	14/09/2022 13:50	0	N
27150	1	09/09/2022 13:17	1	14/09/2022 13:50	0	Y
27150	1	12/09/2022 10:24	2	14/09/2022 13:50	0	Y
27150	1	13/09/2022 08:39	3	14/09/2022 13:50	0	Y
27150	1	14/09/2022 08:54	4	14/09/2022 13:50	0	Y
27150	1	14/09/2022 13:50	5	14/09/2022 13:50	1	Y
999999	0	14/07/2022 08:47	0	18/07/2022 13:43	0	N
999999	0	18/07/2022 13:43	0	18/07/2022 13:43	0	N

Note that the Denied = 1 streak is particular to and ID so for ID 999999 I wouldn't want to display anything.

As for why Repeated Expired = 1 on that row, that was my mistake and I've corrected that above.

Thanks!