Running count not based on a table column

[mrivney]

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.

[Fowmy]

@mrivney 

UIse this measure:

RC = 

COUNTROWS(
    WINDOW(
        0,ABS,
        0,REL,
        SUMMARIZE( ALLSELECTED( 'SLR' ) , 'SLR'[LasName] ),
        ORDERBY( 'SLR'[LasName] , ASC )
    )
)

[mrivney]

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.

[danextian]

Hi @mrivney ,

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

[mrivney]

Hi Danextian,

  Here's the data:

PlayerId	LastName	FirstName	CountryDescription
11	Smit	Hans	USA
20	Powers	Jes	USA
33	Smithy	Ellen	Mexico
40	Valon	Deborah	Mongolia
50	Palmers	Janice	USA
119	Taddy	Dwight	USA

[mrivney]

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.

[danextian · Accepted solution]

Hi @mrivney ,

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.