Forum Discussion
Count repeating values
Hi, I have for example the following data:
| Product | Values |
| 1 | 20 |
| 1 | 20 |
| 2 | 15 |
| 3 | 10 |
| 1 | 5 |
I want to count how many times the value 20 returns in a row. So, for this case it will be two. How can I calculate that in DAX?
Hi Anonymous ,
There should be a column that indicates which row comes before which. To do that, add an Index column in Power Query then create a calculation to identify whether a value in the current row is the same as the next row and vice versa. If what you are looking for is as below, the solution is in the attached pbix.
5 Replies
Hi Anonymous ,
This depends on how you want the result to be shown in a visual. For 20 alone,
= CALCULATE ( COUNTROWS ( 'table' ), FILTER ( 'table', 'table'[Values] = 20 ) )= CALCULATE ( COUNTROWS ( 'table' ), 'table'[Values] = 20 )For every unique value (can either bve a calc column or a measure, use calc column if you want to be able to use the count in a slicer)
= CALCULATE ( COUNTROWS ( 'table' ), ALLEXCEPT ( 'table', 'table'[Values] ) )danextian I think I was not clear enough. I want to count the repeating values. So if the value occurs several times in a row then they must be counted. Otherwise it is just 1. In the example is for product 1 the value 2 times in a row, so the count will be two. The other products are just 1.
Hi Anonymous ,
There should be a column that indicates which row comes before which. To do that, add an Index column in Power Query then create a calculation to identify whether a value in the current row is the same as the next row and vice versa. If what you are looking for is as below, the solution is in the attached pbix.