Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks everyone!
Giovanni 🙂
P.S. @v-juanli-msft
@Anonymous
Have you Tried to increase the Data Load
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@aj1973 @ but why I would do that? I don't see that is an issue. The real problem is in the DAX, it is not fully optimized and that's probably causing the issue.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Yes the DAX might need to be optimized, but the data load of the Power bi desktop and the RAM of his machine can also make the difference.
I increased the data load to 8M and my machine is 32 of RAM and the report loads smoothly.
However i still agree that the DAX needs optmization as the model and data is pretty much big.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@aj1973 It loaded for me without any problem, without any change, still not sure if loading has something to do with the setting you mentioned, or it could be, don't know but one thing for sure, measure is not very well written,
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I could also load the visual in PBI Desktop without errors. The problem come when I load the report into the service.
Hi @Anonymous
After deep investigation I found the following:
To focus more on the issue, the main problem reside in the measure Importo:
I replaced every line by a variable and I created 2 new measures TT1 and TT2 to compare all 4 variables in Importo ---->
Conclusion :
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.
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 slicers
Here is attached the changes I made
Regrads
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
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:
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:
The remaining problem in your solution is that if I drill down a Month, the visual of "Istogramma Centri - Voci" is flattened. See image.
In the original report "Importo" measure is sliced by CostCenters (CodiceCentroAnalisi). See image.
Hope to have been helpful.
Thanks thanks thanks a lot for your help!
🙏
Yes i got the error trying to publish it to the service.
Resources Exceeded
This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed.
Please try again later or contact support. If you contact support, please provide these details.
More details: 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.
Activity ID: cd769258-8b75-41c6-8c9b-e6c3bb9c8ddb
Correlation ID: d40aec16-b210-631a-639d-5b543120afff
Request ID: 53b79242-f169-ee17-029d-65d6bfe02c03
Time: Thu Jul 02 2020 14:03:10 GMT-0400 (Eastern Daylight Time)
Service version: 13.0.13736.165
Client version: 2006.4.01715-train
Cluster URI: https://wabi-canada-central-redirect.analysis.windows.net/
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
@Anonymous it surely happens when DAX is not performing and taking all the resources. We can connect but I'm busy for the next few days. If you explain what you are trying to achieve with that importo measure, it might be quicker.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi guys and thank You.
I think the same as @parry2k that the problem could come from the measure.
I don't know if increasing the value suggested by @aj1973... the error comes out when I try to consult the report in the Cloud Service Microsoft Server. How can that parameter in PBI Desktop could affect the behaviour of the Service?
@parry2k, i am trying to visualize the measure "importo" sliced by Months.
If you want better understand what does the measure is calculating I suggest you to read this explanation in an older post:
https://community.powerbi.com/t5/Desktop/Complex-calc-of-values-in-a-difficult-data-model-with-hiera...
Here you can find an earlier version of the report, where someone help me to make the right measure... and then the report with the measure done.
Let me know if you can understand more from this. My Skype id is giovannibracci if you wanna communicate faster and need more information.
Thank you both!!!!!
@Anonymous Hey I can assist, I downloaded your file and looked at the importo measure. I think it will be easier if you explain what you are trying to achieve.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |