This article shows a basic technique used to sort months according to a fiscal calendar, by using a couple of calculated columns and the “sort by column” feature of Power BI.
The @Test column contains either the Margin or the Total Cost result, depending on whether the Sales Amount of the given color is higher than the AverageSales per color or not. This is a useless calculation – we are not interested in the result, but rather in how the DAX engine computes it.
The engine starts by computing the AverageSales variable. Then, it has a choice. The first option is to compute the value of the condition at line 8 for each color – checking whether Sales Amount for the given color is larger than AverageSales – before computing either Margin or Total Cost, choosing to calculate only the one that is required by the result of the condition. This behavior is the most intuitive and it is known as strict evaluation in Analysis Services and Power BI. Another well-known name for this technique is short-circuit evaluation.
There is another way of computing the same result. The engine could compute every measure (Margin, Total Cost, and Sales Amount) for every product color. Once all these values are available, the engine would check the condition for each color and choose whether to use the previously computed value of Margin or Sales Amount. This technique is known as eager evaluation and consists in precomputing all the possible values that could be returned by a conditional statement, choosing which value to use case by case at a later time.
Which of the two techniques is better? You already know the answer: it depends. Though strict evaluation may look more efficient, this is not always the case. Before moving further, consider a simple example. What if the expression for the @Test column looked like this: