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
ajinkya1907
Helper II
Helper II

Identify Batsman with Max runs in a sesson

Hi,

 

I am using Cricket related data for one of my report. I would like to find out Batsman with Max runs in a sesson.  Kindly help how to achive that.

 

Formula for Max score is 

Highest_Score = VAR temp =

    FILTER (

        SUMMARIZE (

            'deliveries',

            'deliveries'[match_id],

            deliveries[batsman],

            "runs", SUM ( deliveries[batsman_runs] )

        ),

        [runs] >= 100

    )

RETURN

MAXX(temp, [runs])

1 ACCEPTED SOLUTION

Hi,

 

This is clunky, but I'm only just learning DAX.

 

Measure = VAR temp = SUMMARIZE(Deliveries, Deliveries[match_id], Deliveries[batsman], "batsman_runs_match", SUM(Deliveries[batsman_runs]))
RETURN MINX(TOPN(1, temp, [batsman_runs_match], DESC),[batsman])

 

I haven't tested it on the whole dataset, as I didn't want to sign up for Kaggle, but it works on my subset.

 

Chris

View solution in original post

9 REPLIES 9
Chris99
Advocate III
Advocate III

Hi,

 

What does your data look like, and what results are you getting at the moment?

Deliveries_Data_Set.JPG

 

So to get runs scored by batsman in any match, We need to group by on Match_ID, Batsman.

 

entire data set can be found on

https://www.kaggle.com/manasgarg/ipl/data

@ajinkya1907

 

Hi, this helps you?

 

Top1.png

Regards

 

Victor

Lima - Peru




Lima - Peru

Thanks @Vvelarde & @Chris99 for your inputs. 

 

@Vvelarde, This solution will work if I consider entire table. To find score of every match, I need to group by first & then calculate Max score. I have given the DAX function used in the problem statement. 

Hi,

 

Are you trying to find the top scorer in each match? I am unsure what you mean by "sesson".

 

Chris

I am assuming that he means working out which batsman has the highest scoring single innings over the course of the season of something resembling cricket, so he needs to group by match number and batsman and then sum on runs in the process


wrote:

Hi,

 

Are you trying to find the top scorer in each match? I am unsure what you mean by "sesson".

 

Chris


 edit - I assume OP isn't aware of cricinfo's statsguru that'll tell him everything he wants, even the slogfest that is T20

Hi,

 

This is clunky, but I'm only just learning DAX.

 

Measure = VAR temp = SUMMARIZE(Deliveries, Deliveries[match_id], Deliveries[batsman], "batsman_runs_match", SUM(Deliveries[batsman_runs]))
RETURN MINX(TOPN(1, temp, [batsman_runs_match], DESC),[batsman])

 

I haven't tested it on the whole dataset, as I didn't want to sign up for Kaggle, but it works on my subset.

 

Chris

Thanks @Chris99 for all your help, It worked fine.

 

@jthomson, Thanks for explaining the scenario & T20 Cricket. 

 

I am trying to understand DAX functions & hence making sample report using cricket data where my focus is moreover to use new measures rather data model changes. 

 

@Chris99 @jthomson @Vvelarde, Feel free to share if you know some nice course for DAX functions in power BI. Thanks Again.

Thanks @ajinkya1907, my first solution Smiley Happy

 

I haven't yet found a good reference, the MSDN examples are not really very well explained, and I've even seen comments that they don't work.

 

If the others know of a source that would help me too.

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.