Count rows dynamically and aggregate

[msuser48]

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:

Product	CountRows
0	3
0	3
0	3
1	6
1	6
1	6
2	7
3	8
4	9
5	11
5	11

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?

[v-yalanwu-msft · Accepted solution]

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:

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]

@msuser48 - This what you're going for?

[msuser48]

@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:

Id	Date	Product	Running Total
1	2017-01-01	0	3
2	2018-01-01	0	3
3	2019-01-01	0	3
4	2017-01-01	1	6
5	2020-01-01	1	6
6	2021-01-01	1	6
7	2021-01-01	2	7
8	2022-01-01	3	8
9	2022-01-01	4	9
10	2017-01-01	5	11
11	2022-01-01	5	11

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):

Id	Date	Product	Running Total
2	2018-01-01	0	2
3	2019-01-01	0	2
5	2020-01-01	1	4
6	2021-01-01	1	4
7	2021-01-01	2	5
8	2022-01-01	3	6
9	2022-01-01	4	7
11	2022-01-01	5	8

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