Kyle took inspiration from the 2026 FIFA World Cup for this week’s challenge, looking at how many miles each team need to travel between their base camp and the stadiums where their group matches are being held. He sourced some data which he provided for download.

Modelling the data

The data provided contained 3 sheets. Kyle gave some hints on how he’d used the data, but nothing specific, so I just related the sheets in the following way, to see whether this would be enough.

I related Schedule to Base Camp, but BEFORE I applied the relationship join condition, I pivoted the Schedule data (as Kyle had hinted this may be required). I did this by

• Adding Base Camp to canvas
• Then adding Schedule to canvas
• Then clicking on the Team A and Team B columns in Schedule, and selecting Pivot
• The renamed Pivot Names field heading to Team A | B
• and renamed Pivot Values field heading to Team1 (I couldn’t call it Team as this field already exists in Base Camp)
• I then created a relationship between Base Camp.Team and Schedule.Team1

The add Venues to the canvas a relate to Schedule on the Venue field.

Creating the Map with all locations

For the maps we need to work with spatial data, and need to define the location of each base camp and each venue. The data sets had longitude and latitude fields for both types of location, but for some reason, both the longitude fields were resolving as string fields. So I had to change these to geographic fields by the following steps

• right click Base Camp > Longitude, and change data type > number (decimal)
• then right click field again , geographic role > latitude
• repeat same 2 steps for the Venues > Laitude (Venues) field.

Once done create fields

Base Location

MAKEPOINT([Latitude],[Longitude])

and

Venue Location

MAKEPOINT([Latitude (Venues)],[Longitude (Venues)])

Then create

Line

MAKELINE([Base Location],[Venue Location])

and then

Distance – Base to Venue

DISTANCE([Base Location], [Venue Location],’mi’)

On a new sheet, add Venue Location to Detail, and Venue to Detail. Change the mark type to circle. Adjust the map background layers so only the Base, County/Region Names and State/Province border options are selected (Map menu > background layers)

Create a parameter to store the name of the selected team we want to focus the data for

pTeam

string parameter defaulted to ‘Austria’

Create a field

Is Selected Team?

Team = [pTeam]

Add this to Colour and Size and adjust accordingly. Make sure the ‘true’ is listed first so that the marks for the selected team are ‘on top’ . This will require the Size to ‘be reversed’

Remove all text from the Tooltip

Drag Base Location onto the display and drop it when ‘Add a Marks Layer’ option appears, which will create a 2nd marks card

Drag this card to be beneath the Venues one. Change the mark type to circle. Add Team to Detail. Add Is Selected Team to Colour and Size. Add Training Site and City to Tooltip and update accordingly.

Then click on the context menu of the Venues marks card, and disable selection

Nowt drag Line on to the canvas to make another marks layer. Again move this marks card to the bottom of the list, so it’s beneath the Base Camp marks card. Add Team to Detail and Is Selected Team to Colour. Create a copy of Is Selected Team (right click field and duplicate to create Is Selected Team (copy)) and add this to Size and adjust. I found I needed a copy so I could have different sizes between the circles and the lines Add Training Site and Distance to Tooltip and update the Tooltip accordingly. Remove row & column dividers.

Creating the ‘Team specific’ map

The easiest way I found to do this initially, was just to duplicate the sheet with the above map, and then add Is Selected Team to the Filter shelf, and set to True. This gives us the display we need, but tooltips need changing.

Re-enable the Venues marks card. We’ll need to display information about the match on the venues tooltip, wihich includes details for the teams playing.

As we initially pivoted the data, this information is now across 2 rows, so we need to create fields to capture both the teams on each row. I used FIXED LoDs for this:

Team A

{FIXED [Match]:MIN(IF [Team A | B]=’Team A’ THEN [Team_1] END)}

Team B

{FIXED [Match]:MIN(IF [Team A | B]=’Team B’ THEN [Team_1] END)}

Add these to Tooltip, along with Training Site, Date, Time (Local) and Distance. Format the Date field to “Month, Day Year” format, and custom format the Time (Local) field to h:nn AMPM. The adjust tooltip to suit.

Now do similar to the Line marks card – add Match to the Detail shelf as a blue disaggregated discrete pill, and then add Team A, Team B, Venue, Date and Time(Local) to the Tooltip and update accordingly.

Create the Bar Chart

On a new sheet, add Is Selected Team and Team to Rows and Distance to Columns. Sort descending. Order so Is Selected Team : True is listed first.

Make the rows a little wider. Add Is Selected Team to Colour. Add Team to Label. Adjust Label so the team name is aligned left and coloured in white bold text. Adjust the Tooltip. Add a Reference line to the Distance axis to show per cell the value of the sum of the distance. Don’t show line or tooltip.

Format the reference line, so the numbers are aligned in bold font, middle right

Hide the Is Selected Team, Team and Distance headers & axis (right click pill > uncheck show header). Then remove all row/column dividers and gridline, axis rulers etc.

Finally create 2 fields

True

TRUE

False

FALSE

and add these to the Detail shelf (we’ll need them later to ensure the bar doesn’t highlight when we select values).

Creating the match cards

On a new sheet, add Is Selected Team to Filter and set to true.

We need to identify which match is match 1st, 2nd and 3rd. I did this using the dates, but in hindsight could have used the Match field which just contains an unique number per match… anyway…

Match 1 Date

{FIXED [Team_1] : MIN(Date)}

Match 3 Date

{FIXED [Team_1] : MAX(Date)}

Match No for Team

IF [Date] = [Match 1 Date] THEN ‘Match 1’
ELSEIF [Date] = [Match 3 Date] THEN ‘Match 3’
ELSE ‘Match 2’
END

Add Match No for Team to Filter and set to Match 1.

Chang the mark type to shape and set to use a transparent shape (see here for more details on this). Then add Match No for Team, Match, Venue, City (Venues), Distance, Date and Time (Local) to Label. Format the date and time field as you did above.

Create a field

Opponent

IIF([Team_1]=[Team A],[Team B],[Team A])

and add this to Label too. Then adjust the label as required, and align middle left. Don’t show tooltips.

Duplicate this sheet and change the filter to select Match 2 for match 2, and then repeat for Match 3 so you have 3 separate sheets for the matches.

Creating the ‘team’ sheet

On a new sheet, add Is Selected Team to Filter and set to True. Set the mark type to shape and use a transparent shape. Add Team, Distance, Training Site and City to Label and adjust and format accordingly, aligning left middle. Don’t show tooltips.

Building the dashboard and adding the interactivity

I set the background of the dashboard to dark grey and then used layout containers to organise the content, using padding and rounded corners to style as required. The layout of my dashboard is pictured below. It will take some time to get this layout just right, and you might find that in Desktop some text doesn’t display but will when published on Public.

Add a parameter dashboard action to change the team when the bar chart is clicked on

Set Team

on select of the bar chart sheet, set the pTeam parameter with the value from the Team field.

To prevent the selected bar from being ‘highlighted’ when clicked, add a dashboard filter action

Deselect Bar

On select of the bar chart on the dashboard, target the bar chart sheet directly, setting fields true = false.

And that should be it.! My published viz is here

Happy vizzin’!

Donna