Forum Discussion
Optimising DAX Query
Thanks 123abc but the new measure you suggested still only allows for a distinct slicer value being selected. The business case dictates that a user should be able to select multiple slicer values (each linked to a measure) to overlay on a chart visual.
I have been following the other steps you said though and point 2 on reviewing the model has uncovered something - I didn't realise a visual filter could be so detrimental to report performance: https://blog.crossjoin.co.uk/2022/11/10/performance-tuning-table-visuals-with-filters-applied-in-power-bi/
I've learnt a new PBI 'gotcha'! Removing the visual filter has resolved the query performance error.
Thank you for the additional context. Considering that you have multiple measures following a similar format and you're still encountering resource limitations, optimizing your DAX measures further is indeed necessary. Let's explore some strategies:
Consolidate Measures: If your measures share similar logic or calculations, consider consolidating them into fewer measures. This reduces the overall memory footprint of your model.
Review Data Model: Analyze your data model to ensure it's optimized for performance. This includes minimizing unnecessary relationships, reducing the size of tables, and removing unused columns.
Limit Visualizations: Evaluate if all 12 measures need to be displayed simultaneously on the same visual or if you can split them across multiple visuals or pages.
Reduce Complexity: Simplify your DAX expressions wherever possible to reduce computational overhead.
Considering these points, let's revisit your DAX measure to see if we can simplify it further:
Current Market Unit Price =
IF (
ISFILTERED ( _SelectionTable[Selection] ) &&
ISFILTERED ( _SelectionTable[Category] ) &&
SELECTEDVALUE ( _SelectionTable[Selection] ) = "Current Market" &&
SELECTEDVALUE ( _SelectionTable[Category] ) = "Unit Price",
AVERAGE ( 'FACT Table'[Current Market Price] ),
BLANK ()
)
Changes made:
ISFILTERED: Checks if a column has been filtered in the current context. This ensures both "Selection" and "Category" columns are filtered.
SELECTEDVALUE: Returns the value if there's only one distinct value in the specified column within the current filter context. This should handle cases where there's only one value selected for both "Selection" and "Category".
This optimized measure should help in reducing unnecessary calculations and potentially alleviate resource limitations. However, if you're still encountering issues, consider the broader aspects of your Power BI report and data model to identify potential areas for improvement. Additionally, reaching out to Microsoft support or community forums for Power BI might provide further insights into optimizing resource usage for your specific 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.
Thanks 123abc but the new measure you suggested still only allows for a distinct slicer value being selected. The business case dictates that a user should be able to select multiple slicer values (each linked to a measure) to overlay on a chart visual.
I have been following the other steps you said though and point 2 on reviewing the model has uncovered something - I didn't realise a visual filter could be so detrimental to report performance: https://blog.crossjoin.co.uk/2022/11/10/performance-tuning-table-visuals-with-filters-applied-in-power-bi/
I've learnt a new PBI 'gotcha'! Removing the visual filter has resolved the query performance error.