Power BI resource exceeded error in Desktop and service

[MadhuKumar]

Hi All,

Hello! In both the web app and the desktop app, some visuals in our Power BI reports cannot be loaded and viewed. When clicking on the details, it says that the per query memory limits have been exceeded. Please refer to the screenshots. The situation happens for all users of the same report. Recently the current workspace of this report is degraded from premium to PRO. 

Could you please assist here.

Thanks

Madhu

[bcdobbs]

It would be a case of trying to optimize the data model and dax in the measures the visuals are running.

If you've downgraded from premium to pro you now have less memory available.

Can you share your model and some of the dax from your measures?

[MadhuKumar]

Thanks for the quick response Ben.

Here is the screenshot of the data model.

Also find some of the DAX measures as below.

count articles = IF(ISBLANK(Distinctcount([Article ID])),0,Distinctcount([Article ID]))

Dynamic Rank Im =
IF(NOT(ISBLANK([Min Rank Im])), RANKX(
    FILTER(CROSSJOIN(ALLSELECTED(imitation_results), ALLSELECTED(relevant_articles_size_stock)), [Min Rank Im] <> BLANK()),
    [Min Rank Im],
    ,
    ASC,
    Dense
    ) - (Page[Page Value] - 1) * 12)

Volume = "Volume: " & FORMAT(MAX(imitation_results[Sales Volume]), BLANK())

filter_out = SWITCH(
    TRUE(),
    ISFILTERED(remove_delivery_date) &&
    FIRSTNONBLANK(range_sell_in[Confirmed Delivery Date], 1) IN VALUES(remove_delivery_date[Confirmed Delivery Date]),
    0,
    1)

Min Rank = IF(SUM(relevant_articles_size_stock[Stock]) > 0, IF(remove_delivery_date[filter_out] = 1, MIN(sub_cross_results[Rank]), BLANK()), BLANK())

Number of recs = IF(
    ISFILTERED(sub_cross_results[Article ID]),
    CALCULATE(
        COUNTROWS(sub_cross_results),
        FILTER(
            sub_cross_results,
            remove_delivery_date[filter_out] = 1
        ),
        FILTER(
            sub_cross_results,
            sub_cross_results[Rank] <> 1
        ),
        FILTER(
            sub_cross_results,
            relevant_articles_size_stock[Sum Stock] > 0
        )
    ) + 0
, "")

recs =
VAR count_rec = CALCULATE(DISTINCTCOUNT(sub_cross_results[Recommendation Article ID]), KEEPFILTERS(sub_cross_results[Rank] <> 1), KEEPFILTERS(relevant_articles_size_stock[Stock] <> BLANK()), FILTER(remove_delivery_date, remove_delivery_date[filter_out] = 1)) + 0
RETURN
IF(
    ISFILTERED(sub_cross_results[Article ID]),
    count_rec,
    ""
)

[bcdobbs]

Are you able to narrow down which measure triggers the error or do they all?

First impression is that a lot of work is being done in DAX rather than utilising the data model. Things that jump out are the distinct counts and cross joins.

[MadhuKumar]

So, how we can find out which measure is causing the error and what would you suggest here to overcome this problem.

What are your recomendations or best practices to see the visuals in a report with out any resource exceeded errors?

Things that jump out are the distinct counts and cross joins. - Could you plaese explian this clearly, we can not understand it properly.

Thanks

Madhu

[bcdobbs]

Start with a single visual on a page that uses one of those measures at a time. I suspect some will run fine but others will trigger the memory error. We can then try and rewrite those measures to use less resource.

I mentioned CROSSJOIN because it creates a very large virtual table. If you cross join two tables each with 100 rows you get 100 x 100 = 10000 rows. Guessing your tables have a lot more rows. I'd want to understand your model and what the measure does better, and look to either avoid that crossjoin or do it in power query so the result gets compressed and uses less memory.

Likewise DISTINCTCOUNT can cause performance issues... have a read of https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/

[bcdobbs]

Without a total rebuild of the model you might try to optimise the distinct counts. See if any of the idea in:

https://gorilla.bi/dax/optimize-distinctcount/
help.