Performance Boost repetitive Measures

[showy]

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

[v-echaithra]

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-echaithra]

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-echaithra]

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-echaithra · Accepted solution]

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.

[FBergamaschi]

Looking into this, shall come back shortly

Best

[FBergamaschi]

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

[FBergamaschi]

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

[showy]

Hi @FBergamaschi 

thanks for your help.

I tested your method in the actual report. I have also tried a similar method. Unfortunately, it is very slow and the table takes about 30 seconds to load.

I also considered using calculation groups, but I cannot find a suitable solution with that either.

[FBergamaschi]

Can I have the full pbix to try optimizing performance?

[showy]

Thanks for your effort.

Unfortunately, I cannot upload the large data set as it contains company values.

I could create a Dim_Item_extended and a Dim_FCA_Storage that have the same number as the real data.
However, I cannot display the F_Itemledger with the 20 million data points.

Do you have any idea how this could be solved?