Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mrivney
Frequent Visitor

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.
1 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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@mrivney 

UIse this measure:

RC = 

COUNTROWS(
    WINDOW(
        0,ABS,
        0,REL,
        SUMMARIZE( ALLSELECTED( 'SLR' ) , 'SLR'[LasName] ),
        ORDERBY( 'SLR'[LasName] , ASC )
    )
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

mrivney
Frequent Visitor

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
Super User
Super User

Hi @mrivney ,

 

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










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi Danextian,

  Here's the data:

 

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

USA

And here is the visual:

 

mrivney_0-1701724612828.png

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.

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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors