Forum Discussion
Performance Boost repetitive Measures
I have a data model that consists Dim_Item, a Dim_FAC_Storage and a fact table called ItemLedger. These tables are well-structured.
One special feature in the Itemtable is that each article has 9 levels, which represent a pricing assessment.
The report is designed to always show values as of a specific reference date. All values are calculated cumulatively up to that date.
There is currently a matrix in the report that displays both stock quantity and stock value. The stock value should be dynamically changeable via a filter, allowing users to switch between Level 10 and Level 90.
The stock quantity is calculated as sum of ItemLedger[Qty].
The stock value is calculated by multiplying this Quantity by the selected level value from the D_Item table.
Right now, I have created one measure for each level — so 9 measures in total — all identical except for the level used.
Since I also need to calculate the same values for the Start of Month, I now have 18 measures.
To save on additional measures, I used a visual calculation to calculate the delta field.
I believe there must be a much simpler way — ideally, I could combine the base logic for all levels into a single measure instead of maintaining 9.
I already tried using a SWITCH function, and it worked, but performance was extremely poor — the table took around 40 seconds to load when switching the level.
Please note: this test was done on a small data subset. The full dataset includes:
- ~ 1 million articles
- 20 storage locations
- ~ 20 million entries in the ItemLedger table
What would be the best practice for this requirement to achieve high performance?
All relevant files are attached here: https://drive.google.com/drive/folders/1U8yNJY9dl8OzXUTu7AgPfNUThCh12GgS?usp=drive_link
Hi showy ,
When working with a large-scale Power BI data model including a fact table with ~20 million records and ~1 million unique items managing dynamic pricing levels (Level10 to Level90) using separate measures can significantly degrade performance and maintainability. To improve performance and simplify your model, it’s essential to consolidate logic, minimize measure duplication, and reduce computational overhead during visuals rendering.
1. Normalize Pricing Levels in Power Query
2. Create a Disconnected Slicer Table for Price Levels
3. Write a Single Dynamic Measure
Hope this helps.
Warm regards,
Chaithra E.
10 Replies
- FBergamaschiSuper User
Looking into this, shall come back shortly
Best
- FBergamaschiSuper User
How about this approach? A single measure, and it seems pretty fast but you can double check yourself
I shall try load pbix this into your googledrive
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
- FBergamaschiSuper User
I cannot laod there, my version is here, please let me know
https://drive.google.com/drive/folders/1Ec6P0HxEhgDbmjofmAhZiU3uPlAq8FGj?usp=sharing
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
- v-echaithraCommunity Support
Hi showy ,
When working with a large-scale Power BI data model including a fact table with ~20 million records and ~1 million unique items managing dynamic pricing levels (Level10 to Level90) using separate measures can significantly degrade performance and maintainability. To improve performance and simplify your model, it’s essential to consolidate logic, minimize measure duplication, and reduce computational overhead during visuals rendering.
1. Normalize Pricing Levels in Power Query
2. Create a Disconnected Slicer Table for Price Levels
3. Write a Single Dynamic Measure
Hope this helps.
Warm regards,
Chaithra E. - v-echaithraCommunity Support
Hi showy ,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E. - v-echaithraCommunity Support
Hi showy ,
We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.
We truly appreciate your patience and look forward to assisting you further if needed.
Warm regards,
Chaithra E. - v-echaithraCommunity Support
Hi showy ,
We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.