Visual and dax level optimization that causes report slowdown

[sivarajan21]

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('Calendar'[Date])*COUNT(Points[DBName-Point_Id])

var _result = [Count of Exisitng Days]/_total

return _result

It references a dax called Count of Exisitng Days as below:

Count of Exisitng Days = Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date])

Var Ext_dates = COUNTROWS(dates)

return Ext_dates

Now, the problem i have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance

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

Thanks in advance!

@marcorusso @danextian @tharunkumarRTK @Ritaf1983 

[v-bmanikante · Accepted solution]

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

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

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]

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 

[v-bmanikante · Accepted solution]

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

[v-bmanikante]

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