Hi,I currently have a dashboard based on 5 excels and 10 SharePoint lists. To increase the speed of this report I have done mulitple things: Deleted columns I don't need (will it make a difference if it is het last of the applied steps on a query or if you do it when loading the query?)Fiddled with the data load settings (no preview loading and no data/time loading)Minimized string columns to a certain extendAfter doing this the report is still quite slow. Note that the lists and excels are very small at the moment. No more than 10 lines each. With no query having more than 10 columns. I do have a fair amount of slicers on each page (around 6) and read that this could slow down performance. I also have some measures using the 'filter' function, but find no other way of writing them. Does anyone have any experience with this? And how have you resolved it? Kind regards,Matt

God help us you have a SUMX with an ALL filter nested inside another SUMX with an ALL filter. I'm not trying to be mean but that's the DAX equivalent of replacing your car's oil with 5-minute epoxy. On every row of your table, your formula evaluates a formula on every row of the table. Inside that formula, on every row of the table, it evaluates another formula on every row of the table. It then uses these results to generate a summary table that includes every row of the table. You see the problem right? It gets even worse when you put this into a visual. If you're putting this measure next to a column from the table, it must do all those steps a separate time for each member of that column in the visual. It would help to see some sample data so I can get an idea of what kind of tables this is operating on. You can make something fake at mockaroo.com if your real data cannot be shared. How many rows are in this performance snapshot table? Also you're referencing several other measures inside this measure, so it would also be helpful to see the formulas for those measures. Right off the bat I can tell you that the general pattern you're using is not as efficient as it could be, aside from the nested SUMX issue. Written generically, your measure follows this pattern: SUMMARIZE( TableName, TableName[Column1], TableName[Column2], "MeasureName", SUMX( FILTER( TableName, TableName[Column3] = "examplevalue" ), CALCULATE(SUM(TableName[Column4])) ) )That is almost always less efficient than this pattern: ADDCOLUMNS( SUMMARIZE( TableName, TableName[Column1], TableName[Column2] ), "MeasureName", SUMX( FILTER( TableName, TableName[Column3] = "examplevalue" ), CALCULATE(SUM(TableName[Column4])) ) )In your case the speed gain will be pretty minor right now, because the rest of the formula is slowing everything down so much. Still, the final form of this measure should still be switched to this pattern because hopefully we'll find a way to rewrite those nested SUMXs so they're not such a problem. I'll get back to you on the rest of it once I have a little more info from you about how it all works.

What exactly is slow? Is it slow to load in the service, is it slow to refresh the queries, is it slow to add new measures? How many measures are in the data model? Do you have any table or matrix visuals that contain a lot of different measures? Do any of your measures use iterator functions like SUMX, AVERAGEX, etc?

Hi, Sorry for the unclarity. The report is currently slow in the desktop app. The refreshing is quite slow, but acceptable. The slowest part is the loading and filtering of the report through slicers. I have 4 tables that consist of the following type of measure.: Weighted question 2 = SUMMARIZE('Performance Snapshot';'Performance Snapshot'[Team Member];"Weight question two value";SUMX(FILTER(ALL('Performance Snapshot');'Performance Snapshot'[Team Member]=EARLIER([Team Member]));[Number of hours]/SUMX(FILTER(ALL('Performance Snapshot');'Performance Snapshot'[Team Member]=EARLIER([Team Member]));[Number of hours])*[Always on my team])) These tables are used in half the visualizations, but are mostly not matrixes or tables. I also have a measure taking date columns from different lists and combining them into one single date query as follows: Formula: Date Slicer = DISTINCT( UNION(VALUES('Check-In'[End Date]);VALUES('Pulse Survey'[Modified]);VALUES('Performance Snapshot'[Modified]);VALUES('Career Aspirations'[Year]);VALUES('My Strengths'[Year]);VALUES('Firm Contributions'[Year]);VALUES('Goal Setting'[Year]))) Thanks for your help

Oh yeah, nesting multiple SUMXs inside a SUMMARIZE will definitely slow things way down, especially if there are a lot of rows in whatever you're SUMXing. As a first step, I would recommend installing DAX Studio and learning how to use that. You can run test queries to find out how quickly or slowly a particular measure will run, which will help you find the major sources of slowdown. Then you can focus on those measures and see if there is a more efficient way to write them. I'll play around with your example measures to see if I can come up with any better methods.

Thanks, I will have a look at this tool and try to make it more efficient as well.

Report is too slow | Microsoft Fabric Community

17 Replies

Anonymous
Not applicable
9 years ago
What exactly is slow? Is it slow to load in the service, is it slow to refresh the queries, is it slow to add new measures? How many measures are in the data model? Do you have any table or matrix visuals that contain a lot of different measures? Do any of your measures use iterator functions like SUMX, AVERAGEX, etc?
- Matthias93
  Helper III
  9 years ago
  Hi,
  
  Sorry for the unclarity. The report is currently slow in the desktop app. The refreshing is quite slow, but acceptable. The slowest part is the loading and filtering of the report through slicers. I have 4 tables that consist of the following type of measure.:
  
  Weighted question 2 = SUMMARIZE('Performance Snapshot';'Performance Snapshot'[Team Member];"Weight question two value";SUMX(FILTER(ALL('Performance Snapshot');'Performance Snapshot'[Team Member]=EARLIER([Team Member]));
  [Number of hours]/SUMX(FILTER(ALL('Performance Snapshot');'Performance Snapshot'[Team Member]=EARLIER([Team Member]));[Number of hours])*[Always on my team]))
  
  These tables are used in half the visualizations, but are mostly not matrixes or tables.
  
  I also have a measure taking date columns from different lists and combining them into one single date query as follows:
  
  Formula: Date Slicer = DISTINCT( UNION(VALUES('Check-In'[End Date]);VALUES('Pulse Survey'[Modified]);VALUES('Performance Snapshot'[Modified]);VALUES('Career Aspirations'[Year]);VALUES('My Strengths'[Year]);VALUES('Firm Contributions'[Year]);VALUES('Goal Setting'[Year])))
  
  Thanks for your help
  - Anonymous
    Not applicable
    9 years ago
    Oh yeah, nesting multiple SUMXs inside a SUMMARIZE will definitely slow things way down, especially if there are a lot of rows in whatever you're SUMXing. As a first step, I would recommend installing DAX Studio and learning how to use that. You can run test queries to find out how quickly or slowly a particular measure will run, which will help you find the major sources of slowdown. Then you can focus on those measures and see if there is a more efficient way to write them. I'll play around with your example measures to see if I can come up with any better methods.

Forum Discussion

Report is too slow

17 Replies

Related content

Report very slow

Power BI report Server slow

Snowflake connexion too slow

Slowness of the report

Power BI slow