cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

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

Hi @GiovanniBracci 

After deep investigation I found the following:

  • Many Tables in your Model are not related
  • Tables and columns are useless in your model like "Calendario" and "Date"(Can form one Table)
  • Many measures are basically giving out the same exact result
  • In your "Misure su Saldi Unificati", for some measures you are using SUMX instead of simply using SUM to add up the sum of 2 columns from the same table
  • For better performance of DAX measures it is better to use Variables espacially when using conditions(IF..) in Return.
  • In the measure "Importo Ricl. COR" and in the SWITCH function there is a line "Ribaltamenti",[Importo 2 (Ribaltamenti)]" which is useless because it does not exist in the Table.column 'Criteri Inclusione Riclassificato'[Tipo Dato]
     

To focus more on the issue, the main problem reside in the measure Importo:Importo.PNG

I replaced every line by a variable and I created 2 new measures TT1 and TT2 to compare all 4 variables in Importo ---->Importo Measure.PNG

Conclusion : 

  • Vim1 = Vim2 = TT2 no matter what the date "Data" is
  • TT1 = -7.76M no matter what the date filter is
  • All 4 variables do not react to the change in Filters "Tipo Importo" and/or "Indice Ribaltamenti" which is normal beacause the 2 tables are not related to any table in the Model.

In the Sample attached I simplified the Importo measure but was not sure what exactly you wanted to achieve, The report in the service is working fine.

Importo correcto.PNG  

For better performance I also used SUM instead of SUMX in many measures and Variables in "Importo Ricl. COR" measure.

One last thing, in some pages of your report some Slicers are useless and again it is because some Tables are not related like these 2 slicersUsless Slicers.PNG

 

Here is attached the changes I made

 

Regrads

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
Highlighted
Helper I
Helper I

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

Hi @aj1973 ,

thank you for your reply. I've seen some improvements.

Let me try to explain something on the logic of the report and reply to some points you mentioned:

 

  • Many Tables in your Model are not related
    - Some tables are multi-related with relation not active, because I am going to choose the relation to be used in the measure.
    e.g. look at measure "Importo 2 (Ribaltamenti)"
  • Many measures are basically giving out the same exact result
    - This is what it seems to be but if you look at the 2nd page "Ribaltamenti", you will see that [Tot. con Ribaltamenti] and [Importo 1 (Saldi)] are not exactly the same. To understand that you need to investigate the measures chainded to calculate [Tot. con Ribaltamenti]. The logic of what I want to achieve by that measures is well explained here (the names of the tables and fields are changed but you can find the correct explanation with correct names here below).

 

Anyway, 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 "TipoRiclassifcazione" (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).

 

E.G.
Lets say we have 1 record in BALANCES:

CostItem

CostCenter

Year

Month

Value

003104000010

CR-0155

2019

1

85,0

 

 

 

 

 

 

 

 

 

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)

 

 

 

 

 

00,0

 

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

 

 
 

The remaining problem in your solution is that if I drill down a Month, the visual of "Istogramma Centri - Voci" is flattened. See image.

 

c1.JPG

 

In the original report "Importo" measure is sliced by CostCenters (CodiceCentroAnalisi). See image.

c2.JPG

 

Hope to have been helpful.

 

Thanks thanks thanks a lot for your help!

🙏

 
 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors