Consecutive Top 7 finishes in league table

[PetyrBaelish]

I am working on some reports that produce some league tables (it's for a cards game - but imagine something like Formula 1).

I had previously posted on this forum about a DAX formula to calculate a player's consecutive wins, and successfully got the following answer here (I adapted it slightly to exclude a certain "TournamentType" - as per my formula below)

 I was hoping to extend this to count how many tournaments somebody has finished in the top 7 but couldn't figure it out.

My data is as follows in a table called Results:

Looking at that data (particularly those in Bold), what I'm expecting to achieve is as follows:

Kian has finished 3rd, 4th & 5th - his consecutive top 7's is 3

Stacy finished 7th, First Out & 4th - her consecutive top 7's is 1

Gregory finished 6th, then he didn't play, then 4th. His consecutive top 7's is 2 (even though he missed a week - of the times he has played he has consecutively made the top 7).

For reference - any finish could have the text "(L)" on the end of it, which is why in the formula above we're always looking at the first 3 letters of the Result column - essentially if its from 1st - 7th you're in the top 7 or if the first 3 letters are "LOW" or "FIR" you didn't finish in the top 7.

Another concept here is there are seasons - so every 6 months a new season starts - I'm hoping this is irrelevant as I have page level filters / slicers whereby one page there's a league table for the current season and on another page a league table for all league seasons.

What I'm looking for is 3 statistics (I'd appreciate the answer to one which I can use to figure out the other two), as follows:

Let's say for example you go on a run of 5 top 7's, then finish low 2 times, then 3 top 7's, then finish low once (and that takes us to the most recent tournament)

Statistic 1: Your best run of consecutive top 7's. In the example above that would be 5

Statistic 2: Your worst run of not being in the top 7. In the example above that would be 2.

Statistic 3: Your current run (displayed as a positive figure if it's a run of top 7's or a negative figure if it's a run of non-top 7 finishes. The example above would be -1

The intention is for these 3 statistics to be displayed as columns in a visualisation that also displays the player name, points, number of times they've played which are all derived as calculated columns (and some measures, e.g. to calculate %ages) on the results table as outlined above.

This visualisation is on two different report pages - one with a "season" filter and one without.

I'm also looking to maintain my filter to exlcude TournamentType = "Grand Final".

I currently have the following code - it seems to work when I include a visualisation of just a player, tournament date, result and "ConsecTop7" - but when I add this column to my existing league table visualisations as described above, the results are way out. I was aiming for the "Best run of top 7's) - statistic 1 above when I started.

ConsecTop7 = 
VAR Ref_Date =
    CALCULATE (
        MIN ( Results[Tournament] ),
        FILTER (
            Results,
            [Player] = EARLIER ( [Player] )
                && [Tournament] > EARLIER ( [Tournament] )
                && [TournamentType] <> "Grand Final"
                && (LEFT ( [Result], 3 ) = "LOW" || LEFT ( [Result], 3 ) = "FIR")
        )
    )
VAR Ref =
    IF ( Ref_Date = BLANK (), DATE ( 3000, 12, 31 ), Ref_Date )
RETURN
    IF (
        LEFT ( [Result], 3 ) = "1st" || LEFT ( [Result], 3 ) = "2nd" || LEFT ( [Result], 3 ) = "3rd" || LEFT ( [Result], 3 ) = "4th" || LEFT ( [Result], 3 ) = "5th" || LEFT ( [Result], 3 ) = "6th" || LEFT ( [Result], 3 ) = "7th",
        COUNTROWS (
            FILTER (
                Results,
                [Player] = EARLIER ( [Player] )
                    && [TournamentType] <> "Grand Final"
                    && [Tournament] >= EARLIER ( [Tournament] )
                    && [Tournament] < Ref
            )
        )
    )

[v-jiascu-msft]

Hi @PetyrBaelish,

Please check out the details in the attachment. It's a solution based on M and DAX formulas.

Best Regards,

Dale

[PetyrBaelish]

Thank you very much for your help - there's just a couple of issues - which I've tried to delve into but I don't have the skills 😞 

First of all it didn't seem to be treating "First Out" and "LOW" as the same. For example a person finished LOW, then First Out then First Out, but their "worst run (Statistic 2) was showing as 1.

I tried making the following change in the "Added Custom" step:

Original:

= Table.AddColumn(#"Added Index", "Custom", each if Text.Middle([Result], 0, 3) = "Fir" then 1 else if Text.Middle([Result], 0, 3) = "LOW" then 2 else 0)

After my change ("Fir" becomes 2, the same as "LOW"):

= Table.AddColumn(#"Added Index", "Custom", each if Text.Middle([Result], 0, 3) = "Fir" then 2 else if Text.Middle([Result], 0, 3) = "LOW" then 2 else 0)

Unfortunately my visualisations contained no change, both in the above example and below:

Iain has had 1 'non-Top 7' finish - but his 'worst run' was blank where it should be 1.

Also the "current run" (statistic 3) isn't showing correctly - I may not have explained this well enough.

Let's say person A finished, in order: 1st, 2nd, LOW, LOW, First Out 

Let's say person B finished, in order: LOW, LOW, 4th, 4th, 4th

Person A is on a current run of 3 'non Top 7' finishes so his outcome is -3

Person B is on a current run of 3 'top 7' finishes, so his outcome for 'current run' should be 3.

I think from the way I explained it before, I possibly lead you to believe I only expected a 1 or -1, which is not actually what I require - apologies for that. Looking at person B's figures above their current run figure would be:

Week ------ Finish - current run

01/09/2018 LOW ___ -1

08/09/2018 LOW ___ -2

15/09/2018 4th _____ 1

22/09/2018 4th _____ 2

29/09/2018 4th _____ 3

My final visualisation would always show the most up-to-date figure which would be 3, and the week after that would go to either 4 or -1 depending on if the player was in the top 7 or not.

[v-jiascu-msft]

Hi @PetyrBaelish,

I have attached a file in my last reply. Can you modify that file to make it more accurate with your model? That would be easy for us to discuss this puzzle base on the same data.

Best Regards,
Dale

[PetyrBaelish]

Hi Dale,

Unfortunately I can't seem to attach a file directly, here's a link to the file in Dropbox:

https://www.dropbox.com/s/zu8adzwnwivfij7/Consecutive%20Top%207%20finishes%20in%20league%20table.pbi...

This is the original file you provided, just the data has been changed (the changes I made to the formulas above didn't work, so I didn't include them)

Here's what I expect the results to be:

So far the "Best Run" column is working perfectly.

The worst and current runs however aren't. 

Looking at Steve, his runs should be (best / worst / current) 1 / 3 / 1, but they're 1 / 1 / 1 (plus others aren't as I wish either)

I believe this is because it's not counting "First Out" as part of a "bad" run in Steve's case

It also looks to me the current run figure is only acocunting for the most recent week, I want it to account for all weeks until tere has been a change (i.e. a change from a good (top 7) finish to a bad (non-top 7) finish. Soif you're last 5 finishes are (from oldest to newest) 7th, 7th, LOW, LOW, First Out - your current run is a run of 3 non-top 7 finishes which should display as -3

[v-jiascu-msft · Accepted solution]

Hi @PetyrBaelish,

The whole logic is quite clear now. Please check the demo in the attachment. Please also note that I didn't remove the old measures. 

1. Modify the "Added Custom" step in the Query Editor. The "First Out" is bad now.

if Text.Middle([Result], 0, 3) = "Fir" or Text.Middle([Result], 0, 3) = "LOW" then 1 else 0

2. I modified this measure directly.

worstRun =
CALCULATE (
    COUNT ( Results[FinalIndex] ),
    LEFT ( 'Results'[Result], 3 ) IN { "low", "Fir" }
)

3. Create a new measure.

newCurrentRun =
MINX (
    TOPN (
        1,
        SUMMARIZE (
            'Results',
            Results[Player],
            Results[FinalIndex],
            "Counts", IF (
                LEFT ( MIN ( 'Results'[Result] ), 3 ) IN { "Low", "Fir" },
                - COUNT ( Results[FinalIndex] ),
                COUNT ( Results[FinalIndex] )
            )
        ),
        [FinalIndex], DESC
    ),
    [Counts]
)

Best Regards,
Dale