measure in slicer has slowed the matrix considerably. Requesting ideas for improvementI

[arunbyc]

I have a matrix that has presently a million rows and 10 columns. The fact table is a view in SQL database with six dimension tables.

I tried to put a slicer that has MTD, YTD, MTD till last month_end, and YTD till last month_end with the following measure in the slicer. The filter pane for the visual has the condition slicerPeriod = 1.

When I put this slicer on the visual, my matrix has slowed down to a crawl. Every drill down is taking too long. If I remove this slicer and use the standard powerBI date-range slicer, the matrix is quite fast.

Have you ever dealt with a similar situation? What makes this slow down so much? What helped to improve the speed?

slicerPeriod = var selection = SELECTEDVALUE(MTDYTD[period_id])
var today = date(year(today()), month(today()), day(today()) )
var current_mth = month(today)
var current_yr = year(today)
var mth_start_dt = date(year(today),month(today),1)
var prior_mth_start_dt = date(year(today),month(today)-1,1)
var prior_mth_end_dt = mth_start_dt-1
var yr_start_dt = date(current_yr,1,1)
var pryr_start_dt =date(current_yr -1,1,1)
var show_MTD = max(_view_project_cost_detail[Date]) IN filter(Slicer_dates,Slicer_dates[Date]>= mth_start_dt && Slicer_dates[Date] <=today)

var show_Last_Month = if (month(today) >1,

max(_view_project_cost_detail[Date]) IN filter(Slicer_dates,month(Slicer_dates[Date]) = current_mth-1
&& year(Slicer_dates[Date])= current_yr),
max(_view_project_cost_detail[Date]) IN filter(Slicer_dates,month(Slicer_dates[Date]) = 12
&& year(Slicer_dates[Date])= current_yr-1)
)
var show_YTD = max(_view_project_cost_detail[Date]) IN filter(Slicer_dates,Slicer_dates[Date] >= yr_start_dt && Slicer_dates[Date] <=today)
var show_YTD_to_Last_Month = if (month(today) >1,
max(_view_project_cost_detail[Date]) IN filter(Slicer_dates,Slicer_dates[Date] >= yr_start_dt
&& month(Slicer_dates[Date])<current_mth && year(Slicer_dates[Date]) =current_yr),
max(_view_project_cost_detail[Date]) IN filter(Slicer_dates, year(Slicer_dates[Date]) =current_yr-1)
)
var show_custom = max(_view_project_cost_detail[Date]) IN ALL(Slicer_dates[Date])

return
int(switch(selection,
1, show_MTD,
2, show_Last_Month,
3, show_YTD,
4, show_YTD_to_Last_Month,
5, show_custom
))

Thanks

AR

[Kaiqiang_Zhou]

Hi, arunbyc:

       You can use Time intelligent functions to calculate MTD or YTD.Here is the link    https://learn.microsoft.com/en-us/dax/datesmtd-function-dax

By the way, you should create a date table and connect to your fact table before you use Time intelligent functions.

Besides, you can use Field parameters to filter measures. here is the link:

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

f this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[anmolmalviya05 · Accepted solution]

Hi @arunbyc, Hope you are doing good!

The slowdown you're experiencing is likely due to the complexity of the DAX calculations combined with the large dataset in your matrix visual. Here's a detailed breakdown of why this happens and some suggestions to improve performance:

• Complex Calculations for Each Row
• Row Context vs Filter Context
• Slicer Interaction
• Fact Table Complexity

Optimizations to Consider

1. Simplify the Measure Logic

Reduce the complexity of slicerPeriod. For example:

Pre-calculate date ranges for MTD, YTD, etc., in a Date Table.

Use pre-defined columns or flags in the date table for MTD, YTD, MTD till Last Month, etc.

Replace complex VAR calculations with simpler lookups from the date table.

slicerPeriod =

SWITCH(

SELECTEDVALUE(MTDYTD[period_id]),

1, MAX(Slicer_dates[MTD_Flag]),

2, MAX(Slicer_dates[Last_Month_Flag]),

3, MAX(Slicer_dates[YTD_Flag]),

4, MAX(Slicer_dates[YTD_Last_Month_Flag]),

5, 1

)

2.Optimize the SQL View

Review the SQL view that serves as the fact table. Ensure:

Proper indexing on join keys.

Avoid unnecessary columns or calculations in the view.

Pre-aggregate data if possible, especially if many calculations are done in Power BI.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

Subscribe my youtube channel for Microsoft Fabric and Power BI updates: https://www.youtube.com/@AnmolPowerBICorner

[arunbyc]

Thank you so much. Your reply is so useful. 

[v-stephen-msft]

Hi @arunbyc ,

Thanks for reaching out to our community.

It sounds like you're experiencing performance issues with your Power BI report.This is a common issue when dealing with large datasets and complex calculations. Please try the following steps:

1. Reduce Data Model Size:

   • Limit the number of columns and rows in your data model to only what is necessary for your analysis.
   • Use data types that consume less memory (e.g., integers instead of strings where possible).

2. DirectQuery Mode:

   • If your data source supports it, consider using DirectQuery mode instead of Import mode. This can help with performance by pushing the computation to the database server.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arunbyc]

Can you please let me know how to trouble shoot the performance of the code in my measure? As I mentioned, the same report works perfectly well when I use the built-in date range slicer but gets really slow when I use my measure in the slicer. So it probably has something to do with my code or how PowerBI handles this code.  My data has less than a million records. Just a few columns. All fields in the fact table or integers or dates. Maybe you can spot something in the code that can be improved or otherwise, can you please let me know of any tools that can find the bottlenecks? Thanks. AR