Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Running count not based on a table column

Hi,

  I would like to place a running count column on a table visual, that has a value starting from 1 on the first row and incrementing by 1 for each following row.  I know I can do this based on a column.  Here's my measure based on the LastName column where SLR is the table name:  

       

M_RunningCount = CALCULATE (
    COUNTROWS(SLR),
    FILTER ( ALLSELECTED ( SLR), SLR[LastName] <= MAX ( SLR[LastName]) ))
 
  It works fine only if the table is sorted by the LastName column, otherwise the values in the rows do not sequentially increment when reading down the column.  I would like to allow the user to sort on any column they wish, but still have the reported rows serially numbered from 1.  And/or even filtered.  Is this possible?
 
  Note:  the data is imported from Teradata DB.
 
Thanks, Mike.
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi Anonymous ,

     

    I don't think what you're trying to achieve is possible.  For a sequence to work, it must reference a column and changing the order of rows in such a column would result to your sequence changing as well.

6 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Anonymous ,

     

    Please post a usable  sample data (not an image) and your expected result from that.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Danextian,

        Here's the data:

       

      PlayerIdLastNameFirstNameCountryDescription
      11SmitHansUSA
      20PowersJesUSA
      33SmithyEllenMexico
      40ValonDeborahMongolia
      50PalmersJaniceUSA
      119TaddyDwight

      USA

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        And here is the visual:

         

        The data was not sorted.  Note, I don't care if it is sorted or not, but I want the Running Count row to be in ascending sequential order, starting from one.  That regardless of the sort or filter.  Thanks.

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    UIse this measure:

    RC = 

COUNTROWS(
    WINDOW(
        0,ABS,
        0,REL,
        SUMMARIZE( ALLSELECTED( 'SLR' ) , 'SLR'[LasName] ),
        ORDERBY( 'SLR'[LasName] , ASC )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Fowmy,  it works only if the LastName column is sorted in ascending order.  If I sort on the same column, but with descending order, then the first row's running count does not start from 1 but instead is the value that is the maximum row count.  And if I sort on a different column, then the row count is not in sequential order, either ascending or descending.

      Thanks, Mike.