Forum Discussion
Dax optimisation
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.