Dax optimisation

[XeniaLi]

Hi all,

I have a DAX expression that runs fine, until I try to construct the drill through table with multiple columns from different dimension.  I found out that MAX I can bring 7 column, more than 7 column result with constatnt loading time.

The model is star schema with Superfact (Bridge table). Measure needs to bring the valaue based on the visual. 
E.g. if the visual shows Month granularity, like Jan 2023, Feb 2023, April 2023 etc = vlaue of this Month; if visual shows Q1 2023, Q2 2023, Q3 2023, Q4 2023 = then values  for the latest Month in the Quarter March 2023, June 2023, Sep 2023, Dec 2023; if visual selected only year, eg, 2022, 2023, 2024 = then value for the latest Month in the Year, Dec 22, Dec 23, Dec 24

Current DAX expresion
OpenCases =
VAR CurrentDate = MAX('Fact Bridge Period'[ReportingStartDateKey]) --finding latest date for selected date range on the visual--
VAR OpenCases = CALCULATE(COUNTROWS('Fact Bridge Period'),
'Fact Bridge Period'[MeasureKey]=56,
'Fact Bridge Period'[ReportingStartDateKey]=CurrentDate --bring the value that matches Max date for selected visual period--
)
RETURN OpenCases

I wonder if there is a way to optimise this DAX expresion.

TIA

[123abc]

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:

1. 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])
)

1. Use of Relationships: Ensure that your relationships between tables are properly defined. Incorrect relationships can impact the performance of your DAX expressions.

2. 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.

[XeniaLi]

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.

[123abc]

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.