DAX Column/Measure

[arsene49]

Hello,

i'm looking for either column/measure - 
below is the data, i want to know who has won 3 consecutive matches.

ex. Batman has won 1 consecutive 3 matches (matchid=5,6,7)
Ozil has won 2 consecutive 3 matches
Rambo 0

expected output in visual - 

Batman - 1
Ozil - 2
Rambo - 0

Data - 

MatchIDNameResult

1	Batman	L
2	Batman	W
3	Batman	W
4	Batman	L
5	Batman	W
6	Batman	W
7	Batman	W
8	Batman	L
1	Ozil	L
2	Ozil	W
3	Ozil	W
4	Ozil	W
5	Ozil	L
6	Ozil	W
7	Ozil	W
8	Ozil	W
1	Rambo	W
2	Rambo	L
3	Rambo	L
4	Rambo	L
5	Rambo	W
6	Rambo	W
7	Rambo	L
8	Rambo	L

[Ahmedx]

You can also optimize the measure like this:

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhmjJ_yFo7pNmkZA-?e=koDNaG

[arsene49]

Thanks Ahmed for the logic.

i checked the logic but your logic is not working for couple of scenarois like if there are continiuos 4 Wins (it is counting as 2)

ex. for below data, it is showing count as 2 which is wrong. it should be 1

also it shows 4 when there are 6 continious W .instead it should show 2

1	Batman	L
2	Batman	W
3	Batman	W
4	Batman	L
5	Batman	W
6	Batman	W
7	Batman	W
8	Batman	W

[Ahmedx]

try writing like this:
https://1drv.ms/u/s!AiUZ0Ws7G26RhnuWCUbFELKVmBc-?e=yTis0r

[arsene49]

Thanks much Ahmed for this refined logic.

this is not working for below scenario - 

if some has won 6/9 times continiously (WWWWWW), it still shows 1. instead it should show 2/3.

it would be great if you can accomodate this scenario in your formula. appreciate your help on this.

[Ahmedx · Accepted solution]

https://1drv.ms/u/s!AiUZ0Ws7G26RhwLHF2DXsrLJHiT4?e=qMjLdY

[arsene49]

Thanks Ahmed. appreciate your help here.

[Ahmedx]

it is also possible to make a calculation without creating additional physical tables

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhmdfULmtuu2pyhAV?e=lZbcDi

[Ahmedx]

1) You need to create an additional table, exactly like this:

2) write the measure like this:

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhmUKoNk-kkGvKSiU?e=kHyHrc