How to count consecutive occurrences of values in DAX and reset the count for each new group?

[yohanTE66]

Hello, I have this problem with PowerBI,

So I have this column named Secondary code that can only have two values: A and B. I want to sort the Secondary code by date and then count the consecutive occurrences of A and B for each name and surname.

For example, I would have (i'm looking for the expected value) : 

I also made the exemple sorted by name surname so it's easier to picture :

Have a good day !

[johnt75]

You could create a calculated column like

Code & number = 
VAR MinDateEver = CALCULATE( MIN( 'Table'[Date] ), REMOVEFILTERS() ) -1
VAR MaxDateEver = CALCULATE( MAX( 'Table'[Date] ), REMOVEFILTERS() ) + 1
VAR CurrentDate = 'Table'[Date]
VAR CurrentCode = 'Table'[Secondary code]
VAR PrevOtherCode = COALESCE( 
    CALCULATE( 
        MAX( 'Table'[Date] ), 
        ALLEXCEPT( 'Table', 'Table'[Name], 'Table'[Surname] ),
        'Table'[Date] < CurrentDate,
        'Table'[Secondary code] <> CurrentCode
    ),
    MinDateEver
)
VAR NextOtherCode = COALESCE( 
    CALCULATE( 
        MIN( 'Table'[Date] ), 
        ALLEXCEPT( 'Table', 'Table'[Name], 'Table'[Surname] ),
        'Table'[Date] > CurrentDate,
        'Table'[Secondary code] <> CurrentCode
    ),
    MaxDateEver
)
VAR NumInWindow = CALCULATE( 
    COUNTROWS( 'Table' ),
    ALLEXCEPT( 'Table', 'Table'[Name], 'Table'[Surname], 'Table'[Secondary code] ),
    'Table'[Date] > PrevOtherCode && 'Table'[Date] < NextOtherCode
)
RETURN CurrentCode & "_" & NumInWindow

[yohanTE66]

So i figured what the problem was about the code you sent, you apply to NumInWindow the length of a consecutive serie, its not what i'm looking for, i would like the index of a consecutive serie, like if i have for one distinct Name and Surname A A A A A A B B B B B B A A A B B A A A A B A B A B i would want it to be 
A_1 A_1 A_1 A_1 A_1 A_1 B_1 B_1 B_1 B_1 B_1 B_1 A_2 A_2 A_2 B_2 B_2 A_3 A_3 A_3 A_3 B_3 A_4 B_4 A_5 B_5 is it clearer ?

[johnt75 · Accepted solution]

I think you can do it by creating 2 calculated columns. First you need to identify each time a person changes from 1 status to the other

Change = 
VAR PrevCode = SELECTCOLUMNS(
    OFFSET( -1, ALL( 'Table'[Name], 'Table'[Surname], 'Table'[Date], 'Table'[Secondary Code] ),
        ORDERBY( 'Table'[Date], ASC ),
        PARTITIONBY( 'Table'[Name], 'Table'[Surname] )
    ),
    'Table'[Secondary Code]
)
VAR Result = IF( ISBLANK( PrevCode ) || PrevCode <> 'Table'[Secondary Code], 1 )
RETURN Result

You can then count the number of changes which happen before the current row

Code & number = 
VAR CurrentCode = 'Table'[Secondary Code]
VAR CurrentDate  = 'Table'[Date]
VAR CurrentName = 'Table'[Name]
VAR CurrentSurname = 'Table'[Surname]
VAR PriorChanges = FILTER(
    ALL( 'Table' ), 
    'Table'[Name] = CurrentName && 'Table'[Surname] = CurrentSurname
    && 'Table'[Date] <= CurrentDate && 'Table'[Secondary Code] = CurrentCode
    && 'Table'[Change] = 1
)
VAR Result = 'Table'[Secondary Code] & "_" & COUNTROWS( PriorChanges )
RETURN Result

[yohanTE66]

Ty it worked !

[yohanTE66]

Hey thanks for helping, I tried and  it is quite close but it still doesnt work the way i want, note that the example i gave you was wrong and that i changed it.  

[bhanu_gautam]

@yohanTE66 

In Power Query Editor, add an index column:

Go to Add Column > Index Column > From 1.

Create a calculated column to identify the groups of consecutive 'A' and 'B' values:

DAX
Group =
VAR PrevCode =
CALCULATE(
MAX('Table'[Secondary code]),
FILTER(
'Table',
'Table'[Index] = EARLIER('Table'[Index]) - 1
)
)
VAR PrevGroup =
CALCULATE(
MAX('Table'[Group]),
FILTER(
'Table',
'Table'[Index] = EARLIER('Table'[Index]) - 1
)
)
RETURN
IF(
'Table'[Secondary code] = PrevCode,
PrevGroup,
'Table'[Index]
)

Create a calculated column to generate the expected values:

DAX
ExpectedValue =
VAR CurrentGroup = 'Table'[Group]
VAR CurrentCode = 'Table'[Secondary code]
VAR GroupCount =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Group] = CurrentGroup
)
)
RETURN
CurrentCode &
IF(
GroupCount > 1,
"_" &
RANKX(
FILTER(
'Table',
'Table'[Group] = CurrentGroup
),
'Table'[Date],
,
ASC,
DENSE
),
""
)

[Deku]

Think the example is wrong, on the second image at the bottom should it return b6?

[yohanTE66]

You are right I changed the Exemple ! ty