Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PetyrBaelish
Resolver III
Resolver III

Consecutive Data

 

This is a continuation of the thread below, which I'd already marked as solved, but have since noticed a problem:

https://community.powerbi.com/t5/Desktop/Consecutive-Top-7-finishes-in-league-table/m-p/561314#M2647...

 

I needed help with a formula to calculate people's consecutive finishes in a weekly game of cards based on whether they finished in the top 7 or not. I wanted to know somebody's best run (consecutively finishing in the top 7), worst run (consecutively failing to finish in the top 7), and their current run.

 

The dummy files we worked with seemed to work but when I've used my real data, I've exposed a problem with the current runs formula.

 

Below is a link to my file (with anaonymised real data). On the Current tab, on the top left visual, underneath the horizontal blue line there's an entry for a player called "Billy". His current run is showing -3 (the minus figure reflecting that this is an outside of the top 7 finish). However if we look at the bottom right visual, Billy has played 4 times, after the first 3 times he failed to make the top 7 and his current run would have bene -3, however his final tournament to date he did finish 7th so his current run should be 1, not -3.

 

I'm not sure why the formula is failing to take into account Billy's final tournament.

 

https://www.dropbox.com/s/6embrngp5nf4odp/DUMMY%20PKR%20Reports%20V3.pbix?dl=0

 

 

1 ACCEPTED SOLUTION

Thanks for your response.

 

Your formula didn't work, but I managed to figure out the problem. The column FinalIndex was of type "Text" where it needed to be numeric - as such the value 9 was taking precedence over the vlue 10 in this case.

 

I have therefore stuck with the "Current" formula provided as a solution in the original post.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Employee
Employee

Hi @PetyrBaelish,

 

Does this work in your scenario:

Current2 = 
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], ASC
    ),
    [Counts]
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your response.

 

Your formula didn't work, but I managed to figure out the problem. The column FinalIndex was of type "Text" where it needed to be numeric - as such the value 9 was taking precedence over the vlue 10 in this case.

 

I have therefore stuck with the "Current" formula provided as a solution in the original post.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.