March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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.
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.
@msuser48 - This what you're going for?
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:
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.
User | Count |
---|---|
122 | |
99 | |
89 | |
74 | |
66 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |