Power Pivot: SUMX Performance – “IF Statements”

There are many great articles on how to improve SUMX performance – a quick Google turned up alternatives to nesting SUMX and redundant measures inside SUMX.

One important issue that I feel is often ignored is the use of “complex calculations” inside of a SUMX.  Although I haven’t found an official definition, I can, at the very least, tell you that it means an IF statement.  These calculations are often the culprit behind slow models designed by inexperienced DAX users.

To demonstrate this, I created the below 10 million row table using PowerBI:

Continue reading “Power Pivot: SUMX Performance – “IF Statements””

Power Pivot: SUMX Performance – “IF Statements”

Power Query: Self-referential Scripts

This article will demonstrate the value of a self-referential Power Query script in an Excel timesheet forecast workflow.

What is a self-referential  Power Query script?  It is a script whose input and output are the same table.  This is useful in scenarios where user input must be maintained while a table is transformed.

Continue reading “Power Query: Self-referential Scripts”

Power Query: Self-referential Scripts