Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
Solved! Go to 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
Hi,
What does your data look like, and what results are you getting at the moment?
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
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
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.
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |