Forum Discussion

cullo6's avatar
cullo6
Frequent Visitor
9 years ago
Solved

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.

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

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

     

     

    Regards,

5 Replies

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

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

       

       

      Regards,

      • cullo6's avatar
        cullo6
        Frequent Visitor
        9 years ago

        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.