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
  
  

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