Touchdown Problem Solved, and DAX COUNTROWS Function

In the last post I was struggling with horribly-designed source tables. To be honest, I never found a way to relate those two tables to each other, even through other intermediate tables that initially seemed promising.

But I DID find two columns in my Plays table itself that saved my bacon – [OffensiveTeamScoreAtPlayStart] and [OffensiveTeamScoreAtPlayEnd].

A simple calc column subtracting the two and now I have [PtsScoredOnPlay]:

=[OffensiveTeamScoreAtPlayEnd]-[OffensiveTeamScoreAtPlayStart]

And that appears to give me what I want – see the picture at right.

(I was a little suspicious at first that the Offensive team could score 2 pts, since only defenses can score safeties, but then I realized these were 2-pt conversions by the offense – see how subtle “business rules” can be?)

OK, so with that calc column in hand, and my knowledge that all Touchdowns score 6 pts, I can go back to my PivotTable and add a measure:

   =CALCULATE(
         COUNTROWS(Plays),
         Plays[PlayTypeName]=”RUN-Run”,
         Plays[PrimaryRoleName]=”Runner”,
         Plays[PtsScoredOnPlay]=6
    )

Dissecting the Formula

I’ve broken the formula into separate lines above. Let’s go line by line.

The first line is just the CALCULATE function. I have previously described CALCULATE as a supercharged version of SUMIF. That bears repeating. Think “improved SUMIF.”
The second line is the expression to be aggregated. You are not limited to SUM. In fact you can place many custom expressions here. In this case I am using the COUNTROWS function. I will explain that below.
The remaining 3 lines are just conditional filters, like the “if” part of SUMIF, or more accurately, SUMIFS, since there are more than one. Notice that the last one filters down to just plays that scored 6 pts, which are touchdowns. The other two filters insure that this was a Rushing play, and the current player was indeed the one carrying the ball.

COUNTROWS is another good (new) friend

Longtime PivotTable users are familiar with a common trick. When you want to count the number of rows from your source data that meet various criteria (in a PivotTable), you find a column in your data that is guaranteed to be unique, and then you add that field to your Values area, and change the “Summarize By” to count.

And if you didn’t have a column that contained unique values, well, you created one.

With COUNTROWS, you no longer have to do that. COUNTROWS(<TableName>) works every time.

Tying it all together

OK, then, the formula above says: “In my Plays table, count the number of rows that are rushing plays, where the current player is the one carrying the ball, that resulted in 6 pts.”

Does it work? You bet. PivotTable on left, ESPN on right. (Priest Holmes, 2004).