cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cullo6 Frequent Visitor
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.

Forum.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator 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:

 

1. Add a WinValidation column.

 

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] )
        )
    )
)

7.PNG

 

 

Regards,

View solution in original post

4 REPLIES 4
cullo6 Frequent Visitor
Frequent Visitor

Re: Count last(actual) consecutive sorted rows

It´s possible? Help me, please.

Moderator 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:

 

1. Add a WinValidation column.

 

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] )
        )
    )
)

7.PNG

 

 

Regards,

View solution in original post

cullo6 Frequent Visitor
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.

Moderator 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,

Helpful resources

Announcements
October 2019 Community Highlights

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.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

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

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 310 members 3,055 guests
Please welcome our newest community members: