Forum Discussion

showy's avatar
showy
Helper II
1 year ago
Solved

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 le...
Need Help
Show and Tell
Tips and Tricks
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    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.

showy's avatar
showy
Helper II
1 year ago

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's avatar
    FBergamaschi
    Super User
    1 year ago

    Can I have the full pbix to try optimizing performance?

    • showy's avatar
      showy
      Helper II
      1 year ago

      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?