This is a powerful function for advanced measures used correctly it give great flexibility in how you slice and dice the data. It will be much easier to explain the CALCLATETABLE function when described as part of a scenario. Let us assume we are asked to identify the count of orders where the Order QTY is > 20000. Basically this allows us to find out how many bulk orders we get.
The first step in this case will be to identify the number of orders where the QTY is > 20000. This can be achieved as shown using the below function
CALCULATETABLE('Fact Purchase','Fact Purchase'[Ordered Quantity]>100)The above function creates a virtual table storing the results for the FACT Purchase table for all rows where the QTY >20000.
EXCALCULATETABLE = COUNTX(
CALCULATETABLE('Fact Purchase','Fact Purchase'[Ordered Quantity]>20000)
,'Fact Purchase'[Ordered Quantity]
)
Now that we have the internal table with the rows that are above 20000 orders we can perform a count on this table. The output of the above Measure will look as shown below
Notice that we have 2949 orders which are considered bulk as opposed to the 8367 other orders. If you have been following the series you will now as the question but can’t we achieve the same result using COUNX (CALCULATE, FILTER). Yes we can but as you will see in the next post we wouldn’t have a table as the output and as result we wouldn’t be able to perform additional calculations on it.
The SQL equivalent of the above function would be similar to
SELECT *
FROM (
SELECT *
FROM fact.[Order]
WHERE Quantity > 20000
) a
Please Consider Subscribing
