topic Count number of consecutive values and sequence in DAX Commands and Tips

Count number of consecutive values and sequence

Anonymous — Sun, 27 Feb 2022 11:05:05 GMT

Hi all,

Hopefully someone can point me in the right direction.

I am looking to count the number of occurrences of consecutive point won within a tennis match. I would like to count how many times a player wins just 1 point, 2 points in a row, 3 points in a row, etc.

For example:

Point won by

Player A

Player B

Player A

Player B

Here player A has won 1 point 2 times

Player B has won 2 points in a row once, and also 3 points in a row once.

I would like to have a measure for each sequence values (ie a measure for how many times player A wins 1 point in a row, another measure for when player A wins 2 points in a row, another for how many times they win 3 points in a row, etc.

I suspect I will need to use EARLIER function and an index column but not sure how to put it all together.

Any help would be much appreciated.

Thanks

Marc

Re: Count number of consecutive values and sequence

Jihwan_Kim — Sun, 27 Feb 2022 13:02:55 GMT

Hi,

I am not sure if I understood your question correctly, but please check the picture down below and the attached pbix file.

All steps of measures are written down in the attached file.

Re: Count number of consecutive values and sequence

CNENFRNL — Mon, 28 Feb 2022 01:44:28 GMT

Excel worksheet formula is powerful enough to deal with this simple quesiton

Re: Count number of consecutive values and sequence

Anonymous — Tue, 01 Mar 2022 11:06:02 GMT

Hi Jihwan_Kim and CNENFRNL

Thank you both for your replies.... They both work great! I will now look to see if I can fully understand the workings of each solution 😊

Thanks again it's much appreciated