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

by | Mar 20, 2024 | The Data School, Yi Gao

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:

Yi Gao
Author: Yi Gao

Yi has a master’s degree in data science from The University of Sydney and a background in engineering and manufacturing. She is passionate about finding insights from large and diverse datasets and applying them to real-world scenarios. In her previous role at Daimler China, she analysed vehicle usage data, provided recommendations and created dashboard for internal customers. In her spare time, she enjoys photography, especially of animals and her two sons, and cooking traditional Chinese food.