Forum Discussion

Kumar11109's avatar
Kumar11109
Icon for Helper IV rankHelper 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 consecut...
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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft 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