How to create a calculated column as a change counter for another column?

[Nietzsche]

Hello everyone,

I would like to create a calculated column that keep count of changes in each row in another column.

See image below.

Everytime value in the Type column changed to another value or blank/null, the calculated column counter should increase by 1.

I have tried with the RANK() function but can not seem to get it to work. Any help would be highly appreciated.

Thank you for reading.

[DataNinja777]

Hi @Nietzsche ,

You can achieve the desired output by creating an Index column using Power Query, as the table in your data model does not inherently store the order in which it is displayed.  Once index column is created in Power Query, create a change counter dax column to identify the change in Type from the previous row.  

Change Counter dax = 
IF(
    'Table'[Index] = 1,
    0, -- No change for the first row
    IF(
        'Table'[Type] <> 
        CALCULATE(
            MAX('Table'[Type]),
            FILTER(
                'Table',
                'Table'[Index] = EARLIER('Table'[Index]) - 1
            )
        ),
        1,
        0
    )
)

Then, you can sum it up cumulatively based on the order of the Index column.

Change counted =
CALCULATE (
    SUM ( 'Table'[Change Counter dax] ),
    FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
) + 1

The resultant output is as shown below which matches with your required output. 

I have attached an example pbix file.  

Best regards,

[Anonymous · Accepted solution]

Hi @Nietzsche,

In my testing, an index column is created in Power Query.

Then add a calculated column (ChangeCounter) to determine whether the value of the current row has changed from the previous row.

If there is a change, it is 1, and if not, it is 0.

ChangeCounter = 
VAR CurrentRow = 'Table'[Type]
VAR PreviousRow = 
    IF(
        'Table'[Index] = 1, 
        BLANK(), 
        CALCULATE(
            MAX('Table'[Type]), 
            FILTER(
                'Table', 
                'Table'[Index] = EARLIER('Table'[Index]) - 1
            )
        )
    )
RETURN
    IF(
        ISBLANK(PreviousRow) || CurrentRow <> PreviousRow, 
        1, 
        0
    )

Finally, add a calculated column (CumulativeChangeCounter) to show the sum of the number of changes in the current column compared to all previous columns.

CumulativeChangeCounter = 
CALCULATE(
    SUM('Table'[ChangeCounter]), 
    FILTER(
        ALL('Table'), 
        'Table'[Index] <= EARLIER('Table'[Index])
    )
)

Best Regards,

Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.