Forum Discussion

ajinkya1907's avatar
ajinkya1907
Helper II
8 years ago
Solved

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.

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi ajinkya1907

     

    How about this one?

     

    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] )

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi ajinkya1907

     

    How about this one?

     

    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] )
    • ajinkya1907's avatar
      ajinkya1907
      Helper II
      8 years ago

      Well That works just fine. Zubair_Muhammad, I am your fan buddy.

       

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi ajinkya1907

         

        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