Optimising DAX Query

[BITomS]

Hi all,

I am getting an error for Resource Governing: This query uses more memory than the configured limit etc... on a specific visual. I have been optimising my PBI model to move logic closer to the source instead of the user, but there is a type of DAX measure i'm using specific to the problem visual which i'm hoping someone from the community may be able to help me optimise, as I believe this may be causing the issue.

For context, the measure is checking for 2 slicer selections and if these are both selected, then I want the measure to return a result. If not both selected, then it returns blank. This is to avoid a graph becoming too 'busy' when a user is viewing it, so it only shows measures a user specifically wants. Here is the measure:

Current Market Unit Price =

IF (

    ISFILTERED ( _SelectionTable[Selection] ),

    VAR LogicTest =

        COUNTROWS (

            DISTINCT (

                FILTER (

                    _SelectionTable,

                    _SelectionTable[Selection] = "Current Market"

                        && _SelectionTable[Category] = "Unit Price"

                )

            )

        ) = 1

    RETURN

        IF ( LogicTest, AVERAGE ( 'FACT Table'[Current Market Price] ), BLANK () ),

    BLANK ()

)

I am unable to share the pbix file, but can provide any additional context. Any help is greatly appreciated!

[123abc]

PLZ TRY THIS:

Current Market Unit Price =
IF (
HASONEVALUE ( _SelectionTable[Selection] ) && HASONEVALUE ( _SelectionTable[Category] ),
IF (
VALUES ( _SelectionTable[Selection] ) = "Current Market" && VALUES ( _SelectionTable[Category] ) = "Unit Price",
AVERAGE ( 'FACT Table'[Current Market Price] ),
BLANK ()
),
BLANK ()
)

1. HASONEVALUE: Checks if there's only one value in the specified column within the filter context. This is used to ensure that only one selection is made for each slicer.

2. VALUES: Returns the unique values in a column under the current filter context. We use it to check if the selected value matches the criteria "Current Market" for Selection and "Unit Price" for Category.

3. Instead of using ISFILTERED and then COUNTROWS and DISTINCT, we directly check if there's exactly one value for each slicer selection.

[BITomS]

@123abc firstly, my apologies, as I didn't say explicitly enough there are multiple measures following this format (12 measures in total), so a user can make multiple selections e.g. cost or unit price and current market, market average, min, max etc...

So the HASONEVALUE approach does not work here as there could be more than one value selected by a user. However, your reply has inspired some further thinking, so thank you! I don't think I can avoid the ISFILTERED, so the DAX is now as follows:

Current Market Unit Price =

IF (

    ISFILTERED ( _SelectionTable[Selection] )

        && CONTAINS (

            _SelectionTable,

            [Selection], "Current Market",

            [Category], "Unit Price"

        ),

    AVERAGE ( 'FACT Table'[Current Market Price] ),

    BLANK ()

)

However, I am still getting the exceeded available resources error message on the visual (published to a shared capacity) 🤔 Perhaps the 12 measures is too many? Unless I can simplify the above DAX even further?

[123abc]

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:

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

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

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

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

[BITomS · Accepted solution]

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

I've learnt a new PBI 'gotcha'! Removing the visual filter has resolved the query performance error.