Optimize DAX complex measure due to 1GB RAM overflow in PBI Service

I am a Power BI PRO license user.

I have a complex measure that in MICROSOFT Service results in a bad error due to RAM overflow:

Resource Governing: The memory used by the query exceeded the configured limit. The query or calculations referenced by it might be too memory intensive. Please consider simplifying the query or calculations. If the dataset is hosted on a dedicated capacity/server, you may also reach out to your capacity/server administrator to see if the per-query memory limit can be increased. Additional information: Requested 1048577KB, Limit 1048576KB.

You can download the pbix file from here.

The page where the visual is located is the 2nd visible (Istogramma Centri - Voci).

Could someone help me optimizing the measure or telling me how solve the issue maybe in a commercial way, if the measure results to be correct?

Here below, a detaild description of the table of the data model, also with table names translated in english for a better comprehension of the model and measure I need.

nyway, here you are the description of the tables (with correct names) and what I needed to calculate:

  • Riclassificato is a table that we could also call SCHEMAS (Contains the Riclassified Budgets that one can choose):
    We can start from the table called " Riclassificato " that is simply a list of Riclassified Budget schemes that one can choose from. The selected scheme is the one I can see represented in the bigger visual on the first page of the report.

  • AnagraficaRiclassificazioni is a table that we could also call SchemaConstructor (Contains the structures of the Riclassified Budgets):
    By the field " TipoRiclassifcazione " (that we could also call Schema), the Riclassificato table filters the AnagraficaRiclassificazioni ( SchemaConstructor ) table.
    For each “T ipoRiclassifcazione " (Schema), AnagraficaRiclassificazioni (SchemaConstructor) table purpose is to define the structure of the selected Riclassified Budget based on a " GruppoRiclassificazione " (Group) and " GruppoRiferimento " ( TotalizeIntoGroup ) hieracy, as you can see in the " Path " and " Level #” fields. Each " GruppoRiclassificazione " (Group) corresponds to a line of the bigger visual representing the selected Riclassified Budget.
    This table contains a field called " ChiaveTipoGruppoRiclassificazione " ( KeyGroupType ) that results from the concatenation of " TipoRiclassifcazione " (Schema) & " GruppoRiclassificazione " (Group) fileds.

  • AnagraficaCollegamentiRiclassificazioni is a table that we could also call SchemaBalancesLink (contains the reference to " CodiceVoceAnalisi " that is a Cost Item):
    " ChiaveTipoGruppoRiclassificazione " ( KeyGroupType ) is the link between AnagraficaRiclassificazioni ( SchemaConstructor ) and AnagraficaCollegamentiRiclassificazioni (SchemaBalancesLink) .
    AnagraficaCollegamentiRiclassificazioni (SchemaBalancesLink) shows which is the " CodiceVoceAnalisi" ( CostItem) related to each " TipoRiclassifcazione " (Schema)&" GruppoRiclassificazione " (Group) key.
    This link is what I need to retrieve the values related to that " CodiceVoceAnalisi" ( CostItem ) in the SaldiAnaliticaUnificato (BALANCES) table.
    Let’s see SaldiAnaliticaUnificato ( BALANCES) .

  • SaldiAnaliticaUnificato ( BALANCES) (contains the values):
    AnagraficaCollegamentiRiclassificazioni (SchemaBalancesLink) is linked to SaldiAnaliticaUnificato ( BALANCES) table by " CodiceVoceAnalisi" ( CostItem ) field.
    Each record of SaldiAnaliticaUnificato ( BALANCES) contains a set of values for a specific " Anno " (Year) & " Mese " (Month) period and also a specific " CodiceCentoAnalisi " ( CostCenter ).
    Indeed, a " CodiceVoceAnalisi" ( CostItem ) can be itemized into many " CodiceCentoAnalisi " ( CostCenter ) so that in SaldiAnaliticaUnificato ( BALANCES) there can be many records related to the same " CodiceVoceAnalisi" ( CostItem ) and same " Anno " (Year) & " Mese " (Month) period but with different Cost Centers " CodiceCentoAnalisi ".
    Indeed, when you select a specific " GruppoRiclassificazione " (Group) of the selected Riclassified Budget into the main visual of the report, the 2 visual below would list the Cost Items " CodiceVoceAnalisi" but also the Cost Centers " CodiceCentoAnalisi " linked to that " Group " " GruppoRiclassificazione ". The different kind of amounts are all summed up by specific measures, thanks to the hieracy defined in the AnagraficaRiclassificazioni ( SchemaConstructor ) table.
    In SaldiAnaliticaUnificato ( BALANCES) we have a field that is Key to reach RibaltamentiAnalitica (REALLOCATIONS) table, formed by the concatenation of " Year " (Anno), " Month " (Mese), " CostItem " (CodiceVoceAnalisi ) & " CostCenter " (CodiceCentroAnalisi).

  • RibaltamentiAnalitica (REALLOCATIONS) :
    Each record in the table contains the specific amount for a particular combination of period (" Year " (Anno) & " Month " (Mese)), " CostItem " (CodiceVoceAnalisi ) and " CostCenter " (CodiceCentroAnalisi).
    However, there are 2 fields about Cost Center (CodiceCentroAnalisi). One for the Cost Center of ORIGIN and one from the Cost Center of DESTINATION.
    This means that I can have a useful record to move the specified amout from a CostCenter of Origin (the one coming from BALANCES ), to a Cost Center of Destination (defined in REALLOCATIONS ).

Lets say we have 1 record in BALANCES :

|CostItem |CostCenter|Year |Month|Value|
|003104000010|CR-0155 |2019|1 |85,0|

And 3 records in REALLOCATIONS :

|CostItem |CostCenter ORIGIN|CostCenter DESTINAT.|Year |Month|Value |
|003104000010|CR-0155 |CR-0155 |2019|1 |85,0 (subtract from balances)|
|003104000010|CR-0155 |CM-7060 |2019|1 |-80,0 (add to balances) |
|003104000010|CR-0155 |CM-7064 |2019|1 |-5,0 (add to balances) |

CostCenter ORIGIN = “Centro Analitica Provenienza”;
CostCenter DESTINAT. = “Codice Centro Analisi”.

This situation means that if I choose to include the reallocations, the amount calculated earlier and based only on BALANCES table should be affected by the changes of REALLOCATIONS table.

Looking at the example, as a result of considering REALLOCATIONS:

  • I should not find in the details of main visual – actually no more – the CR-0155 cost center, because its entire amount of 85 is inside the first record in REALLOCATIONS with the same origin & destination (attention… don’t let you be confused by the operator! You will find the inverted mathematical operator. Actually you should consider postitive amounts in Riallocations as to subtract from BALANCES and negative amounts in REALLOCATIONS as to add to BALANCES ). This means that CostCenter CR-0155 resulting from BALANCES has been totally emptied and that the amount is converged into CM-7060 and CM-7064.
  • This also mean that CR-0155 should disappear from the smaller visual with the CostCenter details and in its place should come up CM-7060 (80,00) and CM-7064 (5,00).

Hope to have been helpful.

Thanks thanks thanks a lot for your help!

Giovanni :slightly_smiling_face: