Taggin consecutive attempts as one

[Kumar11109]

Dear All, 

I am trying to tag the same resources which have accessed consecutively by a group. The tag should remain the same, for ex. 

if resource 123 is accessed by group 098, 5 times consecutively for the first time then its tag should be 1 for all, similarly for group 876. But the next time the group accesses it then it should be 2. 

Sample data: Link

Please help me with this. 

Thank You, 

[v-yulgu-msft · Accepted solution]

Hi @Kumar11109,

Add an index column under Query Editor first.

Then, please refer to below DAX formulas to new calculate columns.

flag =
IF (
    Test_1[Resource]
        <> CALCULATE (
            LASTNONBLANK ( Test_1[Resource], 1 ),
            FILTER ( Test_1, Test_1[Index] = EARLIER ( Test_1[Index] ) - 1 )
        )
        || Test_1[Group]
            <> CALCULATE (
                LASTNONBLANK ( Test_1[Group], 1 ),
                FILTER ( Test_1, Test_1[Index] = EARLIER ( Test_1[Index] ) - 1 )
            ),
    1,
    0
)

Tagging =
CALCULATE (
    SUM ( Test_1[flag] ),
    FILTER (
        ALLEXCEPT ( Test_1, Test_1[Resource], Test_1[Group] ),
        Test_1[Index] <= EARLIER ( Test_1[Index] )
    )
)

Best regards,

Yuliana Gu