Forum Discussion

thisisausername's avatar
thisisausername
Helper II
7 years ago

Count streak? (consecutive values)

See below my team data:

 

 

I want to capture:

 

The streak - so the count of consecutive Win/Lose/Draw.

The latest streak - what's the current streak?

The latest streak count - the count of the latest streak

 

Is this possible with DAX?

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

     

    this is possible with DAX, but I would recommend to consider using Power Query to create the sequence of consecutive streaks per team.

    My assupmtion: there is just one result per team per day?!

     

    Please create a pbix and or xlsx with sample data, upload the files to onedrive or dropbox and share the link.

     

    Regards,

    Tom

      • TomMartens's avatar
        TomMartens
        Super User
        7 years ago

        Hey,

         

        as I already mentioned I created the sequence using Power Query, as it performs much better even if it requires some "tweeking" of M code, but nevertheless, it's quite fast.

         

        I started with the transform "Group by" in Power Query:

         

        Not it's necessary to tweek the autogenerated M code, for this switch to the Advanced Editor.
        Be aware that the grouping editor can not be opened again, after the M code is adjusted, for this you might want to copy the original line and just comment out the line, use 2 slashes for commenting //
        Discover the M function Table.Group and format code similar to this:

        #"Grouped Rows" = 
            Table.Group(#"Filtered Rows", {"Team", "Result"}, {{"AllRows", 
            each _, 
            type table [Date=date, Team=text, Result=text, Streak=number, Latest  Streak=text, Latest Streak Count=number]}})

        In the second replace the _ with this:

        Table.AddIndexColumn( Table.Sort(_, {{"Date", 0} } ) , "rowindexinsubset" ,1,1 )

        In the third line remove the record (everything between the square bracketes) and the brackets itself.

        After the removal, insert this:

        GroupKind.Local

        Finally it should look like this:

        #"Grouped Rows" = 
            Table.Group(#"Filtered Rows", {"Team", "Result"}, {{"AllRows", 
            each Table.AddIndexColumn( Table.Sort(_, {{"Date", 0} } ) , "rowindexinsubset" ,1,1 ), 
            type table }} , GroupKind.Local)

        After this I expanded the table:

         

        After this I created two calculated columns like so:

        c latest streak count = 
var _team = 'TeamResults'[Team]
var maxDate = CALCULATE(MAX('TeamResults'[Date]) , FILTER(ALL(TeamResults) , 'TeamResults'[Team] = _team))
var lateststreakcount = LOOKUPVALUE('TeamResults'[rowindexinsubset] , TeamResults[Team] , _team , TeamResults[Date] , maxDate)
return
IF(AND('TeamResults'[Team] = _team , 'TeamResults'[Date] = maxDate)
    , lateststreakcount
    , BLANK()
)

        and this

        c latest streak = 
var _team = 'TeamResults'[Team]
var maxDate = CALCULATE(MAX('TeamResults'[Date]) , FILTER(ALL(TeamResults) , 'TeamResults'[Team] = _team))
var lateststreak = LOOKUPVALUE('TeamResults'[Result] , TeamResults[Team] , _team , TeamResults[Date] , maxDate)
return
IF(AND('TeamResults'[Team] = _team , 'TeamResults'[Date] = maxDate)
    , lateststreak
    , BLANK()
)

        After all this the table will look like this, the result matches your expected results at least as far as I understand the data:

        Here you will find a pbix file

         

        Regards,
        Tom