Forum Discussion
Dax optimisation
Certainly, optimizing DAX expressions is crucial for improving performance. In your case, I see that you're using a variable (CurrentDate) to find the latest date for the selected date range and then using it in the filter context. Here are a few suggestions for optimizing your DAX expression:
Avoid Using Variables if Not Necessary: Instead of using a variable to store the CurrentDate, you can directly use the MAX function in your filter conditions. This might help to simplify the DAX expression.
OpenCases =
CALCULATE(
COUNTROWS('Fact Bridge Period'),
'Fact Bridge Period'[MeasureKey] = 56,
'Fact Bridge Period'[ReportingStartDateKey] = MAX('Fact Bridge Period'[ReportingStartDateKey])
)
Filtering on MeasureKey: If the condition 'Fact Bridge Period'[MeasureKey] = 56 is common across multiple measures, you might consider creating a measure that filters based on this condition, and then reusing it in your other measures. This can help in better code organization and potential performance improvement.
MeasureKeyFilter = 'Fact Bridge Period'[MeasureKey] = 56
OpenCases =
CALCULATE(
COUNTROWS('Fact Bridge Period'),
MeasureKeyFilter,
'Fact Bridge Period'[ReportingStartDateKey] = MAX('Fact Bridge Period'[ReportingStartDateKey])
)
Use of Relationships: Ensure that your relationships between tables are properly defined. Incorrect relationships can impact the performance of your DAX expressions.
Consider using FILTER and VALUES: Depending on your data model, you might experiment with using the FILTER and VALUES functions to improve performance.
OpenCases =
CALCULATE(
COUNTROWS(FILTER('Fact Bridge Period', 'Fact Bridge Period'[MeasureKey] = 56)),
'Fact Bridge Period'[ReportingStartDateKey] = MAX('Fact Bridge Period'[ReportingStartDateKey])
)
Remember, the optimization depends on the specific characteristics of your data model and the query patterns in your report. It's a good practice to test the performance impact of different optimizations and choose the one that works best for your scenario.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Hi thanks for your answer.
I think this poart of code might not work:
'Fact Bridge Period'[ReportingStartDateKey] = MAX('Fact Bridge Period'[ReportingStartDateKey])
The reason I used variables coz CALCULATE do not support comparing a column to a an aggregate value. I will test if wrapping MAX part in FILTER function helps.
Thanks for the idea.
You're correct, and I appreciate your clarification. In DAX, you can't directly compare a column to an aggregate value outside of a context transition. The FILTER function can indeed be useful in such scenarios. If you encounter issues, another common approach is to use the CALCULATETABLE function with VALUES to get a single-column table containing distinct values of 'Fact Bridge Period'[ReportingStartDateKey] for the current context.
Here's an example using CALCULATETABLE:
OpenCases =
CALCULATE(
COUNTROWS('Fact Bridge Period'),
'Fact Bridge Period'[MeasureKey] = 56,
'Fact Bridge Period'[ReportingStartDateKey] IN VALUES('Fact Bridge Period'[ReportingStartDateKey])
)This expression leverages VALUES to obtain a table of distinct ReportingStartDateKey values in the current context and then uses the IN operator to filter based on that list. This should address the issue of comparing a column to an aggregate value. Please test this approach in your specific context to ensure it meets your performance and functionality requirements.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.