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
but any other iterator function would work here as we have only one row of data.`MAXX()`

## 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!!