Forum Discussion
Increase the loading speed of a table visualization
Hi everyone,
I have this semantic model
I have a table visualization with more than 90 columns. Around 80 of these, are measures that are updated dinamically based on a date filter from the dim_calendar table.
Most of my measures are like this one:
X =
VAR CurrentDate =
MAX ( 'Dim_calendar'[Date] )
RETURN
CALCULATE (
MIN ( 'Table1'[X] ),
'Table1'[Start_Date] <= CurrentDate
&& 'Table1'[End_date] >= CurrentDate
)
However, the loading speed of my table visualization could be a little faster. How can i improve it?
Thank you very much
elcamino You could try this alternative:
X = VAR __CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR __Table = FILTER( 'Table1', [Start_Date] <= CurrentDate ) && [End_Date] >= CurrentDate ) VAR __Result = MINX( __Table, [X] ) RETURN __ResultThat said, 90 columns with 80 of those being measures is a lot of computation.
Hi elcamino ,
Such a huge table for sure will create a very large loading time.
If most of your measures are based on the same calculation have you tried using a Calculation group that changes the way the calculation is done?
So you would basically do the measures with the MIN syntax and then on the calculation group you would do the variation.
This would be similar to this:
x_ = MIN(Table[X]) Calculation group = VAR CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR Result = CALCULATE(SELECTEDMEASURE(), Table[Start_Date] <= CurrentDate && Table[End_date] >= CurrentDate ) RETURN ResultNot sure how the other measures are calculated but you can do something similar for the other measures that are different calculated:
Calculation group = VAR CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR Result = CALCULATE(SELECTEDMEASURE(), Table[Start_Date] <= CurrentDate && Table[End_date] >= CurrentDate ) RETURN IF( SELECTEDMEASURENAME() IN {"A", "B", "C"}, SELECTEDMEASURE(), Result)for this syntax measures A, B and C will be calculated normally and the other will be based on the filter.
But I would advise you to do a different table, or if the users really need this type of visualization I would give them the chance to use the Excel connection to the semantic model and not online experience.
elcamino - To add to the execllent DAX based solutions here.
The best way to make this table load quicker would be to put the 80 measures into a field parameter, and ask your users to pick the most useful 5. These are the ones that load in the table as default, the others can be added to the table / matrix as and when users need them.
There's absolutely no way people are looking at all 80 measures every time they use this table.
Here's some more information on them: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
9 Replies
- MFelix
Super User
Hi elcamino ,
Such a huge table for sure will create a very large loading time.
If most of your measures are based on the same calculation have you tried using a Calculation group that changes the way the calculation is done?
So you would basically do the measures with the MIN syntax and then on the calculation group you would do the variation.
This would be similar to this:
x_ = MIN(Table[X]) Calculation group = VAR CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR Result = CALCULATE(SELECTEDMEASURE(), Table[Start_Date] <= CurrentDate && Table[End_date] >= CurrentDate ) RETURN ResultNot sure how the other measures are calculated but you can do something similar for the other measures that are different calculated:
Calculation group = VAR CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR Result = CALCULATE(SELECTEDMEASURE(), Table[Start_Date] <= CurrentDate && Table[End_date] >= CurrentDate ) RETURN IF( SELECTEDMEASURENAME() IN {"A", "B", "C"}, SELECTEDMEASURE(), Result)for this syntax measures A, B and C will be calculated normally and the other will be based on the filter.
But I would advise you to do a different table, or if the users really need this type of visualization I would give them the chance to use the Excel connection to the semantic model and not online experience.
- Greg_Deckler
Community Champion
elcamino You could try this alternative:
X = VAR __CurrentDate = MAX( 'Dim_calendar'[Date] ) VAR __Table = FILTER( 'Table1', [Start_Date] <= CurrentDate ) && [End_Date] >= CurrentDate ) VAR __Result = MINX( __Table, [X] ) RETURN __ResultThat said, 90 columns with 80 of those being measures is a lot of computation.
- Elena_Kalina
Solution Sage
Hi elcamino
Regarding your measures, both optimization approaches you mentioned could help:
Using variables more effectively can make your DAX more readable and sometimes more efficient
X = VAR CurrentDate = MAX('Dim_calendar'[Date]) VAR FilteredTable = FILTER( 'Table1', 'Table1'[Start_Date] <= CurrentDate && 'Table1'[End_date] >= CurrentDate ) RETURN CALCULATE(MIN('Table1'[X]), FilteredTable)
The KEEPFILTERS version might perform better in some scenarios as it preserves existing filter context
X = VAR CurrentDate = MAX('Dim_calendar'[Date]) RETURN CALCULATE( MIN('Table1'[X]), KEEPFILTERS( 'Table1'[Start_Date] <= CurrentDate && 'Table1'[End_date] >= CurrentDate ) )
However, I'd strongly recommend reconsidering the visualization design itself(having 90 columns in a single table visual is likely causing performance issues and might not be the most user-friendly approach for analysis) Some suggestions:
Split into multiple table visuals with logical groupings of columns
Use tooltips to show secondary information on hover
Implement drill-through pages for detailed breakdowns
Consider a matrix visual with expandable hierarchies if you have categorical data
Use bookmarks to toggle between different views
- mark_endicott
elcamino - To add to the execllent DAX based solutions here.
The best way to make this table load quicker would be to put the 80 measures into a field parameter, and ask your users to pick the most useful 5. These are the ones that load in the table as default, the others can be added to the table / matrix as and when users need them.
There's absolutely no way people are looking at all 80 measures every time they use this table.
Here's some more information on them: https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
- danextian
Measures are calculated on the fly, which gives flexibility but if you load up a visual with too many of them, especially ones with heavy logic or multiple CALCULATE and FILTER layers, it can slow things down. Power BI has to recalculate each one every time the visual updates (when cross-filtering, slicing, shifting between or expanding hierarchies).
Hi elcamino,
Thanks for reaching out to the Microsoft fabric community forum.
I saw Field Parameters were already suggested (which is a great idea), but having worked on similar models, I’ve found that combining a few strategies works much better when you're dealing with 80+ dynamic measures.
Here’s what’s worked for me:
Combine These 3 Things:
- Field Parameters : Let users pick the measures they care about. This alone cuts load time massively since only selected measures are calculated.
- Calculation Groups : If all your measures repeat similar filtering (like date between [Start_Date] and [End_Date]), move that logic into a Calculation Group. This reduces duplication across measures and simplifies maintenance.
- Visual Cleanup : Split the visual if needed. Use drill-throughs, detail pages, or bookmarks instead of cramming everything into one table.
If the response has addressed your query, please "Accept it as a solution" and give a "Kudos" so other members can easily find it.
Best Regards,
Tejaswi.
Community SupportHi elcamino,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.
Hi elcamino ,
If this solution has resolved your query, kindly mark it as accepted.
Thank You.
