rsQueryMemoryLimitExceeded Error in P&L / Balance Sheet Measure (Small Dataset)

[Raja10ram]

Hi Community,

I am facing the following error while building a Profit & Loss report:

Underlying Error: rsQueryMemoryLimitExceeded
Resource Governance: This query uses more memory than the configured limit.
Consumed memory: 1024 MB, Memory limit: 1024 MB.

📌 Scenario:

• Dataset size is small (not a large fact table).

• Using Import mode.

• The visual is a Matrix with rows from a dimension table (e.g., Schedule Order / BS Order, Year and P&L Amount).

• The measure uses a large SWITCH statement to return different financial measures based on selected description.
  
  

  P&L Amount =

  (SWITCH(

      SELECTEDVALUE('Schedule order'[Description]),

  "          Revenue From Operations",

  CALCULATE( [Revenue From Operations],REMOVEFILTERS('Schedule order')),

  "          Other Income",

  CALCULATE( [other income],REMOVEFILTERS('Schedule order')),

   "Total A.REVENUE",

          CALCULATE( [Total A.REVENUE],REMOVEFILTERS('Schedule order')),

  "          Cost of Material Consumed",

      CALCULATE( [Cost of material consumed],REMOVEFILTERS('Schedule order')),

  "          Purchase of Stock in Trade",

          CALCULATE( [Purchase of Stock in Trade],REMOVEFILTERS('Schedule order')),

  "          Changes in Inventories of Finished Goods & Semi Finished Goods",

          CALCULATE( [Changes in Inventories FG],REMOVEFILTERS('Schedule order')),

  "          Employee Benefits Expense",

          CALCULATE( [Employee Benefits Expense],REMOVEFILTERS('Schedule order')),

  "          Finance Cost",

          CALCULATE( [Finance Cost],REMOVEFILTERS('Schedule order')),

  "          Depreciation and Amortization Expenses",

          CALCULATE( [Depreciation and Amortization Expenses],REMOVEFILTERS('Schedule order')),

  "          Other Expenses",

          CALCULATE( [other Expense],REMOVEFILTERS('Schedule order')),

  "Total B.EXPENSE",

          CALCULATE( [Total B. Expense],REMOVEFILTERS('Schedule order')),

  "Profit Before Exceptional and Extraordinary items and tax",

          CALCULATE( [Profit before 16],REMOVEFILTERS('Schedule order')),

  "Exceptional Items",

          CALCULATE( [Exceptional Items],REMOVEFILTERS('Schedule order')),

  "Profit before extraordinary items and tax",

          CALCULATE( [profit before 20],REMOVEFILTERS('Schedule order')),

  "Profit before extraordinary items and tax",

          CALCULATE( [profit before 20],REMOVEFILTERS('Schedule order')),

  "Tax Expense",

          CALCULATE( [Tax Expense],REMOVEFILTERS('Schedule order')),

  "Extraordinary Items",

          CALCULATE( [Extraordinary Items],REMOVEFILTERS('Schedule order')),

  "Current Tax",

          CALCULATE( [Current Tax],REMOVEFILTERS('Schedule order')),

  "Deferred Tax",

          CALCULATE( [Deferred Tax],REMOVEFILTERS('Schedule order')),

  "Profit After Tax",

          CALCULATE( [Profit Befor 27],REMOVEFILTERS('Schedule order')),

  "Profit (loss) from the period from  continuning operations",

          CALCULATE( [profit 30],REMOVEFILTERS('Schedule order')),

  "Profit/(loss) from discontinuing operations ( after tax)",

          CALCULATE( [profit 36],REMOVEFILTERS('Schedule order')),

  "profit/ (loss) for the period",

          CALCULATE( [Profit 38],REMOVEFILTERS('Schedule order'))

  ))+0
  
  

[v-veshwara-msft]

Hi @Raja10ram ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @Raja10ram ,

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[v-veshwara-msft · Accepted solution]

Hi @Raja10ram ,

Thanks for reaching out to Microsoft Fabric Community.

In addition to the suggestion shared by @AjiteshChurewal , since the error indicates that the query has reached the configured memory limit, please review the Query Memory Limit setting in the Admin Portal under Capacity settings.

If a specific percentage value is configured, you may reset it to 0 and re test the matrix visual. The default value is 0, which allows the maximum memory available for your current SKU.

You may also review the memory limits applicable to your Fabric or Premium capacity and upgrade your SKU to get higher query memory limit.

How to configure workloads in Power BI Premium - Microsoft Fabric | Microsoft Learn

From a DAX perspective, the large text based SWITCH statement combined with repeated REMOVEFILTERS calls can significantly increase query memory usage, especially in a matrix visual with multiple rows and years. You may consider simplifying the logic, reducing repeated context removal, and validating that the dimension table is properly related to the fact table so that aggregation flows through the model instead of being handled inside a single complex measure.

Please let us know if you need any further assistance.

Thank you.

[AjiteshChurewal]

Hi Raja,

Given what you are trying to accomplish, I would recommend linking the dimension table directly with the fact table using a common ID and retrieving the amount through the established relationship.

With an appropriate data model in place, the required values can be derived directly from the fact table aggregation. For this scenario, creating a complex SWITCH-based measure like the current one should not be necessary.