DAX Challenge which leads to so much more….
I understand the challenge and got the answer eventually, however I could not explain why, and it bothered me. Not being a natural programmer, I really have to understand the “why” so here is my explanation which hopefully is clear.
Whilst thinking about it I came across this blog post (note the date and still relevant) from Rob Collie in 2011 which made me feel better about my needing to know why and the fact that I too have forced myself to explain the puzzle.
Facts:
- There are three things to do, two sums and a filter.
- One of the sums is a SUM and one is a SUMX. There is a difference, the SUM works out the total for the whole column, SUMX does it for each row.
- There is no CALCULATE or measure.
Need to define the order to do each bit. You do the bit inside the brackets for each row of the table.
The math is SUM(Orders[Price]) which adds up all the values in the Price column to get 13. There is nothing in Sum to tell it to filter according to the row that you are evaluating so you get the value 13 (there is no Filter being used with the SUM, the filter is used with SUMX).
SUMX is different to sum, it carries out the equation, formulae, instructions for each row. For each row it will check to see if the Price is greater than 2. There are 3 rows. Then it will add up the value of the SUM which is 13 for each row of which there are 3 so the answer will be 39, for every row (hence two lots of sums).
I really struggled with the why for this, I like to know what the reason for the formulae is which helps me make sense of what I’m building, but what I did have to do was think about it and that is one of the joys of DAX.
Unearthed treasures.
The other treasures which I unearthed as a reward for my research was this which is just a perfect summary of key concepts of how the DAX engine calculates measures but also an inspiring blog post from Krissy Dyess on believing in yourself which resonated with me.
The take away from this, is that it’s ok not to understand straight away, you will if you try and you can uncover all sorts of hidden gems in the research.
