cancel
Showing results for
Did you mean:

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

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:

 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?

1 ACCEPTED SOLUTION
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:

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.

3 REPLIES 3
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:

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.

Resident Rockstar

@msuser48 - This what you're going for?

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Helper I

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors