Analyzing and fixing the issues
NOTE: When you have a valid account, you can test DAX Optimizer without activating any license. This page describes how to use the samples provided with the tool.
You start the analysis with the model Contoso 1M - currency conversion in the Sample Walkthrough workspace.
This is the initial version of the model, which has a few issues.
The Overview page shows the issues in the file. You can see the number of issues found, and the number of measures that have issues.
By clicking Issues, we see a list of issues detected in the file.
By clicking Measures, we see a list of all the measures in the model. The measures that have an issue have a different graphical representation.
We sort by Relevance the measures to see the most important issues first. The most important issues are the ones that have a higher impact on the performance of the model.
In the previous screenshot, there are three measures (Margin, Raw Amount, and Total Cost) that have no issues but a relatively high Relevance, larger than other measures with issues such as Margin Currency, Sales Currency, and Total Cost Currency.
However, the measure with an higher performance impact in the model is Sales Amount, which also has two issues. By clicking the measure name, we can see the details of the selected issue.
The list of issues provides details about the relative impact of each issue on the performance of the measure.
If you want to know more details about an issue, you can visit the knowledge base or you can click on “View Issue” to see a detailed description of the issue within the DAX Optimizer user interface.
By expanding the issue description, we have access to a more detailed explanation and to examples of the code that you can use to fix similar issues.
In this case, the issue is related to the callbacks in a SUMX iterator, which is a common anti-pattern in DAX. The Example 1 almost matches the structure of our code, so we can use it to fix the issue.
The original code of our Sales Amount measure is:
Sales Amount = SUMX ( Sales, IF ( Sales[Quantity] > 0, [Raw Amount] ) )
We can fix the code by adapting the Example 1 to our specific case:
Sales Amount = CALCULATE ( [Raw Amount], KEEPFILTERS ( Sales[Quantity] > 0 ) )
You modify the code in your model editor, and you mark the issue as fixed in the DAX Optimizer user interface. Because the code change also removed the iterator, we also fixed the second issue (Context transition in iterator).
Going back to the Measures view, all the measues are still visible. However, you can show only the measures that have issues by clicking the Show Only Measures with Issues button.
The remaining measures have a much smaller relevance. However, this does not mean that they are not important. They simply have a lower priority compared to other measures, but once we fixed the measure with the higher priority, we can focus on the other remaining measures with issues. Click the filter switch to only see the measures with open issues, excluding those where all the issues have been fixed or ignored.
Now there are six measures with a more detailed relevance. In reality, the relevance did not change, it is only normalized to a different scale to make the numbers easier to compare. Indeed, by restoring the visualization of all the measures, including those without open issues, the measure with a higher relevance are now “out of range”, because they have a higher relevance than the measures with open issues.
It is now time to focus on the Margin Currency measure, which has two issues of the same type.
In this case, we should reduce the cardinality of the iterator, because we cannot assume that the result will be the same by moving a measure outside of the iterator. Actually, the result of Conversion Rate seems to depend on the Sales[Currency Code] column because of the CALCULATE function. However, you should verify whether there are other dependencies. By clicking on the Conversion Rate measure name in the code, we can see the corresponding DAX definition.
We repeat this process with LastCurrencyDate and AverageRate measures, just to make sure we are aware of the dependencies involved.
From the code analysis, we establish that Conversion Rate only depends on Date and Currency Exchange tables. The Date table is not directly referenced in the Margin Currency code. However, we should check also the relationships in the data model, because by iterating Sales the context transition is applied to the expanded table, which expands the table by including all the columns on the one-side of any relationship. The relationship diagram is necessary to evaluate this aspect.
The Sales table has a relationship with the Date table, which means that when we iterate Sales, for every context transition on Sales we also filter one row in Date. Therefore, we reach the conclusion that the Conversion Rate measure depends on Date[Date] and Sales[Currency Code]. If we authored the measures, we might already know that, but it is always a good idea to check these dependencies before trying to write a more efficient version of the same code.
The following is the code we currently have, with the Sales tables highlighted in the SUMX iterator.
CALCULATE (
SUMX (
Sales,
VAR _CurrencyCode = Sales[Currency Code]
RETURN
[Margin]
* CALCULATE (
[Conversion Rate],
'Currency Exchange'[FromCurrency] = "USD",
'Currency Exchange'[ToCurrency] = _CurrencyCode
)
)
)
The solution is similar to the Example 3 of the Context transition in iterator issue.
We generate a table with the unique values of the columns included in the dependencies for the measures inside the iterator. This change should be enough to reduce the complexity of the calculation and improve the performance.
CALCULATE (
SUMX (
SUMMARIZE ( Sales, 'Date'[Date], Sales[Currency Code] ),
VAR _CurrencyCode = Sales[Currency Code]
RETURN
[Margin]
* CALCULATE (
[Conversion Rate],
'Currency Exchange'[FromCurrency] = "USD",
'Currency Exchange'[ToCurrency] = _CurrencyCode
)
)
)
We mark Margin Currency as fixed for both issues.
We repeat a similar optimization for Sales Amount Currency and Total Cost Currency. The measure Margin Currency % has a different issue.
The example shows how to transform a division within IFERROR in a DIVIDE function.
Here is the code of the initial Margin Currency % measure:
IFERROR ( [Margin Currency] / [Sales Currency], BLANK() )
We modify the definition as follows:
DIVIDE ( [Margin Currency], [Sales Currency], BLANK() )
We mark the issue on Margin Currency % as fixed.
If we go back to the Issues page, we have other three issues on two measures that have note been fixed yet. However, the relevance of the remaining issues seems very small compared to the issues that we already fixed.
The lower impact of the first measure with open issues (Refund Amount) is also visible in the Measures view. By enabling the visualization of all the measures filtering out the closed issues, the Refund Amount measure appears after many other measures, and its relevance is displayed multiplying its value by 1,000, whereas when there are important issues the relevance is divided by a power of 10 in order to make it readable.
If we highlight the relevance of the more expensive measure (Sales Amount), we can see that its not normalized value is 43,099,041,756.9047, which is 100 millions times larger than the relevance of Refund Amount.
You can continue to fix measures until you have no more issues in the model, but the optimizations we already made should have already a significant impact on the performance of the model. You might have already achieved an optimization goal that is good enough for your requirements. It is up to you when to stop the optimization process.
Note You can apply these changes to the measures in the PBIX file. You will see a clear improvement in the performance of the reports. We already applied these changes to a second version of the PBIX file that has been already analyzed in the samples.
At this point, you may want to validate whether the measures fixed have actually improved the performance of the model. You can do that by running a slow report again and compare the performance beofre and after the changes. You can also analyze the model again with DAX Optimizer to validate that the measure changed in the model do not have any more issues. Indeed, the Unverified badge highlights that the model has measures that have been declared as fixed, but they have not been verified yet by DAX Optimizer.
The final state of the fixed measures is available in the version 1 (v1) of the model Contoso 1M - currency conversion optimized.
You can continue the analysis by verifying the fixed issues.