cancel
Showing results for
Did you mean:
Highlighted
Helper II

## DAX function related Issue

Hi,

I am using Cricket related data for one of my report. I would like to find out Maximum Strike Rate batsmans with more than 100 runs.  I am using below formula

Best Strike Rate =
var temp = FILTER(
SUMMARIZE(deliveries,
deliveries[match_id],
deliveries[Batsman],
"StrikeRate", 100* SUM(deliveries[batsman_runs])/COUNT(deliveries[Batsman])
),SUM(deliveries[batsman_runs]) >= 100)
return
MAXX(temp, [StrikeRate])

It's showing total strike rate & not the max stike rate. Kindly advise. Attached screenshot for your reference.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

## Re: DAX function related Issue

```Best Strike Rate =
VAR temp =
FILTER (
SUMMARIZE (
deliveries,
deliveries[match_id],
deliveries[Batsman],
"StrikeRate", 100 * SUM ( deliveries[batsman_runs] )
/ COUNT ( deliveries[Batsman] )
),
CALCULATE ( SUM ( deliveries[batsman_runs] ) ) >= 100
)
RETURN
MAXX ( temp, [StrikeRate] )```
Regards,
Zubair

3 REPLIES 3
Highlighted
Super User I

## Re: DAX function related Issue

```Best Strike Rate =
VAR temp =
FILTER (
SUMMARIZE (
deliveries,
deliveries[match_id],
deliveries[Batsman],
"StrikeRate", 100 * SUM ( deliveries[batsman_runs] )
/ COUNT ( deliveries[Batsman] )
),
CALCULATE ( SUM ( deliveries[batsman_runs] ) ) >= 100
)
RETURN
MAXX ( temp, [StrikeRate] )```
Regards,
Zubair

Highlighted
Helper II

## Re: DAX function related Issue

Could you please explain why calculate function made the difference? I am not able to understand that part.

Super User I

## Re: DAX function related Issue

Wow I have 2 fans now... Other is my 3 year old son

Actually FILTER function is an ITERATOR.... it creates a ROW context and not a FILTER context

thats why you have to use Calculate

Regards,
Zubair

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors