MIP Logo

DAX-Which country won the most FIFA world cup champion?

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

Do you want to know which country win the most champions in FIFA world cup? I guess you can easily find the answer in the wiki page, but let’s do it in a more complex way-DAX!

You can download the data here.

Step-1: Connect the data

First, you need to connect to data in Power BI. There’s no need to pre-process the data.

Step-2: Build a table chart

Before we try to find the answer, we need to know the award times for each country.  We will create a new column in the table with the formula as below:

award_times =

COUNTX(

    FILTER( award_winners, EARLIER(award_winners[team_id]) = award_winners[team_id]),

    award_winners[team_id]

)

What does this DAX code tell us?

The Syntax of COUNTX is COUNTX(<table>,<expression>), which is used to counts the number of rows that contains a non-blank value or an <expression> that evaluates to a non-blank value, when evaluating an expression over a <table>.

In our code, the formula uses a filter expression to get the rows in the award_winners that meet the condition.

The following steps describe the method of calculation in more detail.

  1. The EARLIER function gets the value of [team_id] , which is “WC-1930” for the current row in the table. In this case, because the process is starting, it is the first row in the table
  2. EARLIER(award_winners[team_id])  equals to the current row in the outer loop.
  3. The FILTER function now returns a table where all rows have a value of team_id equals to “WC-1930” (which is the current value for EARLIER).
  4. The COUNTX function counts the rows of the filtered table and assigns that value to the new calculated column in the current row.
  5. The calculated column formula moves to the next row and repeats steps 1 to 4. These steps are repeated until the end of the table is reached.

Now if we look at the table again in Table View, we can see the award_times value as below.

Now let’s check the award times for each country by ranking the award_times in the table chart in Power BI.

We need to config the column as below.

And we can get a table chart as below:

Step-3: Show the result in a KPI card

To increase the complexity further, let’s try to show the result in a KPI card. We need to create a measure as the formula below:

Most Award Country =
LOOKUPVALUE(award_winners[team_name], award_winners[award_times], MAX(award_winners[award_times]))

This formula means we want to find the [team_name] where [award_times] equals to the largest [award_times].

Now we can show our final result as below:

Share this post