Q1: Player participation trend by season

Season =
LOOKUPVALUE(matches[season], matches[id], deliveries[match_id])
This adds a Season column inside the deliveries table, eliminating lookup issues entirely.

BatterBowlerPlayers =
VAR Batters =
SUMMARIZE (
deliveries,
deliveries[batter],
deliveries[Season])

VAR Bowlers =
SUMMARIZE (
deliveries,
deliveries[bowler],
deliveries[Season])

VAR BothRoles =
INTERSECT(Batters, Bowlers)

RETURN
COUNTROWS(BothRoles)

Use in a Table

Put deliveries[Season] in Rows Put BatterBowlerPlayers in Values

This now works 100% because:

Season is inside deliveries directly SUMMARIZE and INTERSECT work on consistent structure No ambiguous match_id context remains

Q2: Strike rate evolution over years

What is Strike Rate?

Strike Rate (SR) = (Total Runs / Balls Faced) × 100

✅ You Have:

• Table: deliveries
  • batsman_runs, batter, ball, match_id, extra_runs, extras_type
• Table: matches
  • id (match ID), season
• Relationship: deliveries[match_id] → matches[id]

---

✅ Step-by-Step DAX Approach

---

🔹 Step 1: Add Season column in deliveries (if not already)

Season =
LOOKUPVALUE(matches[season], matches[id], deliveries[match_id])

---

🔹 Step 2: Measure: Total Runs (excluding extras)

TotalBatsmanRuns =
SUM(deliveries[batsman_runs])

---

🔹 Step 3: Measure: Balls Faced (exclude extras)

BallsFaced =
CALCULATE(
    COUNTROWS(deliveries),
    ISBLANK(deliveries[extras_type]) || 
    NOT(deliveries[extras_type] IN {"wides", "noballs"})
)

---

🔹 Step 4: Measure: Strike Rate

StrikeRate =
DIVIDE([TotalBatsmanRuns], [BallsFaced], 0) * 100

---

✅ Visual: Strike Rate Over Seasons

Create a Line Chart:

• X-axis: deliveries[Season]
• Y-axis: StrikeRate (measure above)

Optional:

• Add batter to the legend to compare player-wise
• Add slicers for player/team/venue

---

🔍 Bonus: Player-Specific Evolution

To see Virat Kohli’s strike rate over the years:

PlayerStrikeRate =
VAR SelectedPlayer = "V Kohli"  -- or use SELECTEDVALUE(deliveries[batter])
VAR PlayerDeliveries =
    FILTER(deliveries, deliveries[batter] = SelectedPlayer)

VAR PlayerRuns =
    CALCULATE(SUM(deliveries[batsman_runs]), PlayerDeliveries)

VAR PlayerBalls =
    CALCULATE(
        COUNTROWS(deliveries),
        PlayerDeliveries,
        ISBLANK(deliveries[extras_type]) ||
        NOT(deliveries[extras_type] IN {"wides", "noballs"})
    )

RETURN DIVIDE(PlayerRuns, PlayerBalls, 0) * 100

Use it in a table or chart with Season.

---

📈 Sample Output:

Season	SR (Overall)	SR (Virat)	SR (Gayle)
2008	116.5	109.2	148.3
2011	123.6	131.2	162.9
2016	130.2	152.3	149.7

Q3 Bowling economy trends

To analyze Bowling Economy Trends in Power BI, you’ll track how economy rates (runs conceded per over) evolved:

• Over seasons
• By team
• By bowler

---

🎯 What is Bowling Economy?

Economy Rate = (Total Runs Conceded / Overs Bowled)

---

✅ You Have:

• deliveries table:
  • bowler, total_runs, over, ball, extras_type, match_id
• matches table:
  • id, season
• Relationship: deliveries[match_id] → matches[id]

---

✅ Step-by-Step DAX Setup

---

🔹 Step 1: Add Season Column (if not done already)

Season =
LOOKUPVALUE(matches[season], matches[id], deliveries[match_id])

---

🔹 Step 2: Measure: Total Runs Conceded

RunsConceded = 
SUM(deliveries[total_runs])

This includes batsman + extras — which is standard in economy.

---

🔹 Step 3: Measure: Balls Bowled (exclude wides & no-balls)

BallsBowled = 
CALCULATE (
    COUNTROWS(deliveries),
    ISBLANK(deliveries[extras_type]) || 
    NOT(deliveries[extras_type] IN {"wides", "noballs"})
)

---

🔹 Step 4: Measure: Overs Bowled

OversBowled = 
DIVIDE([BallsBowled], 6, 0)

---

🔹 Step 5: Final Measure: Economy Rate

EconomyRate =
DIVIDE([RunsConceded], [OversBowled], 0)

---

✅ Visual: Bowling Economy Trend Over Time

📊 Line Chart:

• X-axis: deliveries[Season]
• Y-axis: EconomyRate
• Legend: bowler (optional) or team via deliveries[bowling_team]

---

✅ Output Table Example:

Season	Avg Economy
2008	7.82
2010	7.43
2013	7.55
2020	8.21

---

🔍 Optional Enhancements

• Per Team Economy Rate:
  Use bowling_team in rows of a table
• Per Bowler:
  Add slicer for bowler or top 10 bowlers
• Phase-Wise (Powerplay/Middle/Death):
  Use over buckets (0–6, 7–15, 16–20)

---

✅ Bonus: Top 5 Most Economical Bowlers per Season

TopEconomicalBowlers = 
TOPN (
    5,
    SUMMARIZE (
        deliveries,
        deliveries[bowler],
        deliveries[Season],
        "Economy", [EconomyRate]
    ),
    [Economy], ASC
)

---