Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a counter in PowerBI based on one column

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_IDCounter
44537_031
44536_031
44526_031
44526_032
44526_033
44526_021
44526_022
44526_023
44526_011
44526_012
44526_013
44526_014
44525_011
44525_031
44525_032
44525_033
44525_021
44525_022
44525_023
44523_011
44523_021
44522_031
44522_021
44522_011
44519_011
Need Help
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

3 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Samarth_18
      Icon for Community Champion rankCommunity Champion
      4 years ago

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