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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
msuser48
Helper I
Helper I

Count rows dynamically and aggregate

This post almost explains perfectly what I want: https://community.powerbi.com/t5/Desktop/Dynamic-Count-of-Rows/m-p/662950

 

The code proposed by parry2k in the post also works:

Count of Products = 
VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) 
RETURN 
CALCULATE( 
    COUNTROWS( Table3 ),  
    ALLSELECTED( Table3 ), 
    Table3[Product] = __currentProduct 
)

 The only issue is: My "product" column is a number from 0 to 5, and I need my "CountRows" measure (remember, it has to be a measure!!!) to instead aggregate like this:

ProductCountRows
03
03
03
16
16
16
27
38
49
511
511

As you can see, there are 3 products = 0, so "countrows" is 3 for each value with "0" in product column.

Then there are 3 products = 1 in the product column, so "countrows" is 6 now, because we aggregate the countrows from product 0 with the countrows of product 1. We continue to aggregate based on the previous product value, as you can see in the table above.

 

How do I make this as a measure?

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @msuser48 ;

Try it.

Count of Products = 
VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) 
RETURN 
IF(HASONEVALUE(Table3[Product]),
CALCULATE( 
    COUNTROWS( Table3 ),  
    ALLSELECTED( Table3 ), 
    Table3[Product] <= __currentProduct 
),COUNTROWS('Table3'))

The final show:

vyalanwumsft_0-1670219718825.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @msuser48 ;

Try it.

Count of Products = 
VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) 
RETURN 
IF(HASONEVALUE(Table3[Product]),
CALCULATE( 
    COUNTROWS( Table3 ),  
    ALLSELECTED( Table3 ), 
    Table3[Product] <= __currentProduct 
),COUNTROWS('Table3'))

The final show:

vyalanwumsft_0-1670219718825.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ChrisMendoza
Resident Rockstar
Resident Rockstar

@msuser48 - This what you're going for?

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



@ChrisMendozaalmost.

But as it was also requested in the post i referred to, this has to work when filters are applied, but it currently does not.

 

Example table with no filters:

IdDateProductRunning Total
12017-01-0103
22018-01-0103
32019-01-0103
42017-01-0116
52020-01-0116
62021-01-0116
72021-01-0127
82022-01-0138
92022-01-0149
102017-01-01511
112022-01-01511

 

If we use a slicer filter for "Date", and set this slicer to be between the range 2018-01-01 to 2023-01-01, we should get this for our "Running Total" measure (as the dates highlighted in bold above are removed):

IdDateProductRunning Total
22018-01-0102
32019-01-0102
52020-01-0114
62021-01-0114
72021-01-0125
82022-01-0136
92022-01-0147
112022-01-0158

 

Again, running total should be a measure, as I understand it will only work with a measure.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.