Forum Discussion

Kumar11109's avatar
Kumar11109
Helper IV
8 years ago
Solved

Taggin consecutive attempts as one

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, 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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