Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

Visual and dax level optimization that causes report slowdown

Hi,   I have this visual as attached,that has lot of visual level filters applied.   it has a dax measure called data completeness as below: Data Completeness =   var _total = COUNT('Cal...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello sivarajan21 ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    danextian Thank you for your quick response.

     

    sivarajan21 

    Thanks for the update. You've already done a good job reducing the visual load to around 11K, but the DAX Studio trace still shows a higher cost. Here are a few suggestions to further improve performance:

    1. Your current DAX uses SUMMARIZE, which is heavy on performance. Instead, try creating a new column that combines DBName-Point_Id and Date (like DBName-PointId_Date) and use DISTINCTCOUNT on that. It’s much faster.
    2. Please try to use Pre-Aggregate at Source or Use Aggregation Tables. Use pre aggregated tables as the base for your visual instead of calculating combinations on the fly.
    3. In your Data Completeness measure, you're calculating totals every time the visual renders. If that value doesn’t change often, pre-calculate it and reference it directly.
    4. lastly in model design:
      If it is possible use single-direction relationships ( I tried , after changing to single-direction performance is somehow improved). Avoid calculated columns or keys with extremely high cardinality that Power BI struggles to compress.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

     

    Regards,

    B Manikanteswara Reddy

  • tharunkumarRTK's avatar
    tharunkumarRTK
    1 year ago

    sivarajan21 

    I made one change to your measures and I can see some good improvement

    Before change: 10.5 seconds

    After the change: 6.5 seconds 

     Measures 

      MEASURE 'DAX'[Count of Exisitng Days] = 

SUMX ( VALUES( Data[Date] ),  CALCULATE(SUMX(VALUES(Data[DBName-Point_Id]),1)    ) )



    MEASURE 'DAX'[Count of Possible Days] = COUNTROWS ( Points ) * COUNTROWS ( 'Calendar' )



    MEASURE 'DAX'[Data Completeness] = DIVIDE ( [Count of Exisitng Days], [Count of Possible Days] )

    Please test the results and also the performance.

    Also, I saw multiple bidirectional relationships in your model, please avoid them. Same is the case with calculated tables. Avoid them

    Need a Power BI Consultation? Hire me on Upwork

     

     

     

    Connect on LinkedIn

     

     

     








    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi sivarajan21 

Instead of using SUMMARIZE, consider using GROUPBY. Additionally, if possible, create a unique key by combining Data[DBName-Point_Id] and Data[Date] at the source or within the query editor. This way, you can simply perform a DISTINCTCOUNT on the precomputed key rather than building the combinations at query time, which will significantly improve query execution speed. Note: this will create an extra physical column, increasing your model's footprint.

 

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
1 year ago

Hi danextian ,

 

I tried my best to bring this matrix performance level to 11k as shown below:

 

Below is the dax studio performance analyser report which shows 20k again:

 

Is there any modeling changes that i can do reduce the time consumption and improve the performance further?

Please advise!

PFA file here PR-419 - Data Coverage - 02.pbix

 

Thanks in advance!

danextian marcorusso tharunkumarRTK 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello sivarajan21 ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    danextian Thank you for your quick response.

     

    sivarajan21 

    Thanks for the update. You've already done a good job reducing the visual load to around 11K, but the DAX Studio trace still shows a higher cost. Here are a few suggestions to further improve performance:

    1. Your current DAX uses SUMMARIZE, which is heavy on performance. Instead, try creating a new column that combines DBName-Point_Id and Date (like DBName-PointId_Date) and use DISTINCTCOUNT on that. It’s much faster.
    2. Please try to use Pre-Aggregate at Source or Use Aggregation Tables. Use pre aggregated tables as the base for your visual instead of calculating combinations on the fly.
    3. In your Data Completeness measure, you're calculating totals every time the visual renders. If that value doesn’t change often, pre-calculate it and reference it directly.
    4. lastly in model design:
      If it is possible use single-direction relationships ( I tried , after changing to single-direction performance is somehow improved). Avoid calculated columns or keys with extremely high cardinality that Power BI struggles to compress.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos " – I’d truly appreciate it!

     

    Regards,

    B Manikanteswara Reddy

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi sivarajan21 ,

       

      We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

       

      If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

      Please don't forget to give a "Kudos " – I’d truly appreciate it!

       

      Regards,

      B Manikanteswara Reddy