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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.

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.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.