Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |