Advent of Code 2020 - Day 1

Power BI / DAX / Power Query / Advent of Code 2020

This will be the first post of a series where I hope to be able to solve the problems from the Advent of Code using only Power BI.

This event, sadly, only came to my attention this year. It started in 2015 and it’s a lot of fun. In these posts I will talk about my thinking and strategy to try and solve these problems.

Without further ado let’s get started:

Part 1

We have a file with a value per line and we need to find two entries that sum to 2020 and then multiply both numbers together. This looks like a cartesian product and as such a good fit for using DAX.

I’ve imported the the data to a table and created the following measure:

Day 1 - Part 1 =
VAR _table1 =
    SELECTCOLUMNS ( 'Day 1 Data', "_1stMember", 'Day 1 Data'[Expenses] )
VAR _table2 =
    SELECTCOLUMNS ( 'Day 1 Data', "_2ndMember", 'Day 1 Data'[Expenses] )
RETURN
    MAXX (
        TOPN (
            1,
            FILTER ( CROSSJOIN ( _table1, _table2 ), [_1stMember] + [_2ndMember] = 2020 )
        ),
        [_1stMember] * [_2ndMember]
    )

The trick here is just to create table variables having columns with different names so that we can use the CROSSJOIN() function. Otherwise you will have an error like this: Function CROSSJOIN does not allow two columns with the same name 'Day 1 Data'[Expenses]..

The rest of the strategy to solve this is:

  • Filter the lines where the sum of the columns is 2020, we will have exactly two:
    • \(x+y = y+x\)
  • Return the first one of those lines
  • Use any of the iterators function and multiply the values together. I’ve used the MAXX() but any other iterator function would work here as we have only one row of data.

Part 2

This problem is just an extension of the previous part. Now we need to find the three numbers that add up to 2020 and multiply them together.

Here’s the measure I’ve came up to:

Day 1 - Part 2 =
VAR _table1 =
    SELECTCOLUMNS ( 'Day 1 Data', "_1stMember", 'Day 1 Data'[Expenses] )
VAR _table2 =
    SELECTCOLUMNS ( 'Day 1 Data', "_2ndMember", 'Day 1 Data'[Expenses] )
VAR _table3 =
    SELECTCOLUMNS ( 'Day 1 Data', "_3rdMember", 'Day 1 Data'[Expenses] )
RETURN
    MAXX (
        TOPN (
            1,
            FILTER (
                 CROSSJOIN ( _table1, _table2, _table3 ),
                 [_1stMember] + [_2ndMember] + [_3rdMember] = 2020 )
            ),
            [_1stMember] * [_2ndMember] * [_3rdMember]
    )

Conclusion

Day one was quite easy for Power BI as these type of calculations are the bread and butter of DAX. Here’s the template of the file I’ve been working on:

I am using a Path parameter expecting the full path to the input files. For me the value of the parameter is:

  • C:\Projects\AdventOfCode\input_day_

So my files follow this pattern

C:\Projects\AdventOfCode\input_day_1.txt
C:\Projects\AdventOfCode\input_day_2.txt
C:\Projects\AdventOfCode\input_day_3.txt
...

Here’s a preview of what’s to come:

Have fun!!

 Share!