This tutorial will discuss about variables, including the FILTER and CALCULATE functions. You’ll understand how these help boost the performance of your DAX queries and provide accurate results.
Variables should be used as much as possible because they can easily locate codes for optimization. They prevent you from getting incorrect outputs and save you time looking for the error. However, variables sometimes prevent a value from being recalculated. So once they’re defined with a value, that value can’t be changed, not even by CALCULATE.
It’s important to know where you should define and use variables because using them inside and outside an iteration will generate different results. They should be defined close to where they’ll be used. This also helps break down long codes, making them easier to understand and debug.
Create DAX Queries With CALCULATE And FILTER
There are different methods in getting results and answers in DAX. You need to develop your own style and flow in creating your report so that you can be comfortable with what you are working on. Even if you have your own style, you still have to follow the specific guidelines, which include using CALCULATE over different methods such as FILTER.
FILTER may send a lot of uncompressed data to the formula engine and can potentially trigger a CallBackDataID. Measures will be used here to show the results of CALCULATE and FILTER. This also has a defined measure which is local to the query. Defined measures can’t be found in the data model but they can develop and troubleshoot your codes faster.
In the first line, you can see a measure that defines a variable for the date. This means that it filters the Fact table for the dates that occur on or after March 31, 2016. You can also see a simple measure of the Total Sales which iterates the Fact Sales table and multiplies Quantity and Current Price.
The next measure is using IF inside SUMX. If you recall from the CallBackDataID module, using an IF inside the SUMX function will create a CallBackDataID.
This measure is filtering the entire Fact Sales table. It removes the filters coming from the Date table.
You have to be careful when filtering an entire table because it will also filter all the columns that are on the one side of the one-to-many relationship.
This measure is the same as the previous measure; the only difference is that it doesn’t filter the entire table. It uses SUMX to only iterate a filtered table.
This is another measure that filters the Fact Sales table but this time it’s only on the column where it’s in.
The last measure is using CALCULATE and the data model to filter and retrieve data.
Run And Check Each Measure
First, run the IF measure. Make sure to clear cache before you run the query and turn on the Server Timings and Query Plan.
Although it provides the correct data, the performance is slow because of the CallBackDataID and the 3 Durations.
If you run the measure that filters the entire Fact Sales table, you can still see the CallBackDataID. It will also generate 15,003 rows and provide incorrect results.
When you run the measure that doesn’t filter all the Fact Sales table, you’ll get the correct results. However, you’ll still see the CallBackDataID and the materialized 15,003 rows, but only have 7 rows of output.
Run the next measure that filters on a column. You’ll get the correct results and eliminate the CallBackDataID. But you can still see that it materialized a lot of rows.
Run the CALCULATE measure. You can see that it only materialized 10 rows and generated the correct data in 5 milliseconds.
It’s the fastest among all the measures above because it doesn’t have the CallBackDataID and pulled only 10 rows. This shows how impressive CALCULATE is in getting quick and precise results.
If you run all DAX queries or measures, you can see which one is the slowest and fastest based on the duration. You can also see the results of each measure.
Conclusion
By moving the filters to CALCULATE, you’ll save time and materialization because the CallBackDataID will be eliminated. You won’t have to iterate the data twice. This will also maximize your DAX’s performance and help you improve your development skills.
Nick
