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

cullo6's avatar
cullo6
Frequent Visitor
9 years ago

It´s possible? Help me, please.

  • 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.

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

        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,

    • Shales's avatar
      Shales
      Frequent Visitor
      3 years ago

      Thank you so much for this. This is exactly what I needed in my report. Perfect! 😀