In this post we explore the use of CALCULATE. Calculate is one of the most frequently used DAX Formulas. This is because it is every flexible and can be used in combination with a number of other DAX expressions. A very common use case for CALCULATE is to SUM or COUNT rows based on a filter. In the previous post we saw how this is achieved by using SUMX. However there is a key distinction between aggregating using SUMX vs CALCULATE.
SUMX can only summarize over a column within the same table. CACULATE can summarize over any table.
Let’s revisit our example from the previous post
You will notice that SUMX only lists columns from the table Order.
Now let’s look at CALCUATE. The syntax or CALCULATE is shown below
ExCAL = CALCULATE(
SUM('Fact Sale'[Quantity]),
FILTER('Fact Order','Fact Order'[Quantity]>100)
)
As you can see we can pass any expression such as count, sum etc. as the first input followed by any filter criteria as the second input. In this case I am filtering the table Fact order for Qty>100 while adding QTY from the Sales table. This will not be possible using SUMX since it requires that SUM be performed on the same table as the filter. Another useful aspect of Filter is that it accepts multiple filters so we can slice and dice the data across multiple dimensions to arrive at the final result.
The SQL equivalent of this function would be a similar to
SELECT SUM(b.Qty) FROM TableA a INNER JOIN TABLE B b ON a.id = b.id WHERE a.Qty > 100
Please Consider Subscribing
