cancel
Showing results for
Did you mean:
cullo6 Frequent Visitor

## Count last(actual) consecutive sorted rows

Hi, I want count last(actual) consecutive rows with text Win or Loss(It matter on latest row) ,sorted by Date with time and by Name like in table below. But if will be measure, it is not necessary to sort by Name, I will use it in Legend. It can be 2 measures, like MaxConLoss and MaxConWin or columns. Can you help me? Thanks. 1 ACCEPTED SOLUTION

Accepted Solutions v-sihou-msft
Moderator

## Re: Count last(actual) consecutive sorted rows

@cullo6

In this scenario, you can create a WinValidation column to tag "1" for each win game. Check the if there is no win game before current row, you just need to sum the WinValidation up to current date. Otherwise you should calculate the max date for the last loss game, then calculate total WinValidation from that date to current date. Please refer to my sample below:

`WinValidation = IF(Table1[Result]="Win",1,0) `

2. Create a calculated column for Consecutive Win:

```Consecutive Win =
SWITCH (
TRUE (),
Table1[WinValidation] = 0, 0,
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
ALL ( Table1 ),
Table1[WinValidation] = 0
&& Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date] <EARLIER ( Table1[Date] )
)
)
= 0, CALCULATE (
SUM ( Table1[WinValidation] ),
FILTER (
ALL ( Table1 ),
Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date] <= EARLIER ( Table1[Date] )
)
),
CALCULATE (
SUM ( Table1[WinValidation] ),
FILTER (
ALL ( Table1 ),
Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date]
> CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALL ( Table1 ),
Table1[WinValidation] = 0
&& Table1[Name] = EARLIEST ( Table1[Name] )
&& Table1[Date] < EARLIEST ( Table1[Date] )
)
)
&& Table1[Date] <= EARLIER ( Table1[Date] )
)
)
)
``` Regards,

4 REPLIES 4
cullo6 Frequent Visitor

## Re: Count last(actual) consecutive sorted rows v-sihou-msft
Moderator

## Re: Count last(actual) consecutive sorted rows

@cullo6

In this scenario, you can create a WinValidation column to tag "1" for each win game. Check the if there is no win game before current row, you just need to sum the WinValidation up to current date. Otherwise you should calculate the max date for the last loss game, then calculate total WinValidation from that date to current date. Please refer to my sample below:

`WinValidation = IF(Table1[Result]="Win",1,0) `

2. Create a calculated column for Consecutive Win:

```Consecutive Win =
SWITCH (
TRUE (),
Table1[WinValidation] = 0, 0,
CALCULATE (
COUNTROWS ( Table1 ),
FILTER (
ALL ( Table1 ),
Table1[WinValidation] = 0
&& Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date] <EARLIER ( Table1[Date] )
)
)
= 0, CALCULATE (
SUM ( Table1[WinValidation] ),
FILTER (
ALL ( Table1 ),
Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date] <= EARLIER ( Table1[Date] )
)
),
CALCULATE (
SUM ( Table1[WinValidation] ),
FILTER (
ALL ( Table1 ),
Table1[Name] = EARLIER ( Table1[Name] )
&& Table1[Date]
> CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALL ( Table1 ),
Table1[WinValidation] = 0
&& Table1[Name] = EARLIEST ( Table1[Name] )
&& Table1[Date] < EARLIEST ( Table1[Date] )
)
)
&& Table1[Date] <= EARLIER ( Table1[Date] )
)
)
)
``` Regards,

cullo6 Frequent Visitor

## Re: Count last(actual) consecutive sorted rows

@v-sihou-msft

Thank you very much for your reply, good job. I just have one thing. I wanted only the current consecutive rows, so in your table it was for "a" 1 (1 win) and for "b" 2 (2 Loss). Loss rows I want count too...it matter on last row. My idea is, doing it like you do, count current consecutive rows, but so if next row will be lesser or equal than previous row, value will be 0, and next rows will be 0 too, but only for this name. Thank you. v-sihou-msft
Moderator

## Re: Count last(actual) consecutive sorted rows

@cullo6

For calculating Consecutive Loss, you just need to same thing as calculating Consecutive Win. Just Create another LossValidation first. then create another same calculated column, and replace the WinValidation with LoassValidation.

We can't combine the Consecutive Win and Loss in same column. Because it's not possible the to have one row directly compare with previous row or next row in DAX. And we have to tag Loss into "0" so that we can cumulative sum the WinValidation to get the Consecutive Wins.

Regards,

Announcements #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 310 members 3,055 guests
Recent signins:
• sartori_r • mmcanirlin • haparvez • Luster • MitchellLowery • alwerth • NPV32 • josehcr • pwagman • h_arieh • Thedatadude • krishnapallavi • ttmai1973 • JamiePBI 