Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yohanTE66
Frequent Visitor

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

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) : 

 

test11.jpg


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

test22.jpg

 


Have a good day !

 

1 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

View solution in original post

8 REPLIES 8
johnt75
Super User
Super User

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

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 ?

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

Ty it worked !

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
Super User
Super User

@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
),
""
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Deku
Super User
Super User

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

You are right I changed the Exemple ! ty

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.