Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 !
Solved! Go to 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
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.
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
),
""
)
Proud to be a Super User! |
|
Think the example is wrong, on the second image at the bottom should it return b6?
You are right I changed the Exemple ! ty
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |