Forum Discussion

Jason254's avatar
Jason254
Frequent Visitor
3 years ago

Resources Exceeded Error when Creating a Profit and Loss Statement

Hi Everyone,

 

I am creating a profit and loss statement using a switch function that helps map different measure results to the different P&L line items. A snippet of the measure is as below:

 

P&L_Financial_value =
SWITCH(
    SELECTEDVALUE('P&L Template'[Line_item_id]),
    "Total volume",[Total volume],
    "Volume-Cider", [Cider_volume],
    "Volume-Mixers", [Tonic_volume],
    "Volume-Iced Tea", [Ice_tea_volume],
    "Volume-Core spirits", [Core_spirits_volume],
    "Volume-Craft spirits", [Craft_spirits_volume],
    "Volume-AO Ciders", [AO_Cider_volume],
    "Total GSV",[Total_GSV],
    "GSV-Cider", [Ciders_gsv],
    "GSV-Mixers", [Tonics_gsv],
    "GSV-Iced Tea",[Icetea_gsv],
    "GSV-Core Spirits", [corespirit_gsv],
    "GSV-Craft Spirits", [Craftspirit_gsv],
    "GSV-AO Ciders", [AO_Ciders_GSV],
    "GSV-Other", [otherincome_gsv],
    "GSV-Merchandise", [Merchandise_gsv],
    "Discounts GSV",[Discount_gsv],
    "Total Excise Duty", [Total_excise_duty],
    "Excise-Cider", [Cider_excise],
 
When building the statement on Power Bi desktop, the visual works fine. However, after publishing the report on service, I get the following error when opening the visual,
 
More details: Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1024 MB, memory limit 1024 MB.
Activity ID: 52be43c1-f11a-4279-96b3-4d1bfc451919
Correlation ID: be516470-2e63-71e4-a394-a8e1d249a584
Request ID: c0d45bd3-3cd2-0bb8-87dd-9c4e0e116e41
Time: Sun Nov 20 2022 15:00:26 GMT+0300 (East Africa Time)
Service version: 13.0.19445.37
Client version: 2211.1.10948-train
Cluster URI: https://wabi-south-africa-north-a-primary-redirect.analysis.windows.net/
 
I am currently using the Power BI pro on service.
Any ideas on how to solve the issue?
Need Help

1 Reply

  • GilbertQ's avatar
    GilbertQ
    Super User
    3 years ago

    Hi Jason254 

     

    This happens when some of your measures consume a lot of memory. Typically it might be one measure causing this issue.


    What I would do is to comment all of them out except 1. Then re-run your visual and see if it works.

    Keep on uncommenting the measures in the switch statement until you get the above error.


    When you get that error find a way to then change the way the DAX is written in the measure to not cause the error.