Creating a counter in PowerBI based on one column

[Anonymous]

Hi all, I am trying to create a calculated column in PowerBI based on another column. There is a column "Shift_ID" that contains a number based on the shift I am looking at. However, dependent on the amount of entries this one might appear multiple times. I would like to create a counter that can give me the number of lines corresponding to that specific number, and that could also be applied as a filter in some of the reports. I know how to do it in Excel with a simpel if-statement (if(row-row[-1]=0;1+row[-1];1), however, that does not seem to apply to Power BI.

Any ideas?

I added the sample Shift_ID data below and the desired outcome in the "Counter"-column.

Shift_ID	Counter
44537_03	1
44536_03	1
44526_03	1
44526_03	2
44526_03	3
44526_02	1
44526_02	2
44526_02	3
44526_01	1
44526_01	2
44526_01	3
44526_01	4
44525_01	1
44525_03	1
44525_03	2
44525_03	3
44525_02	1
44525_02	2
44525_02	3
44523_01	1
44523_02	1
44522_03	1
44522_02	1
44522_01	1
44519_01	1

[Anonymous]

Hi @Samarth_18 ,

Thanks for this. This seems indeed to be what I want to do, however, I do get a Syntax error when adding this in the custom column. I am not really sure why.

The error I get:

Expression.SyntaxError: Token Literal expected.

I have checked and the table-name is indeed "Table", so that cannot be the issue.

Here a screenshot of the error: 

Any idea how to solve this?

[Samarth_18]

@Anonymous Please create this column out of Power query not in the Power query.

[Samarth_18 · Accepted solution]

Hi @Anonymous ,

First create a index column from PQ then create a custom with below code:-

Counter_ = 
CALCULATE (
    COUNT ([Shift_ID] ),
    FILTER (
        ALL('Table'),
        [Index] <= EARLIER ( [Index] )
            && [Shift_ID] = EARLIER ( [Shift_ID] )
    )
)

Output:-

Thanks,

Samarth