Increase the loading speed of a table visualization

[elcamino]

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

[Greg_Deckler · Accepted solution]

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

That said, 90 columns with 80 of those being measures is a lot of computation.

[v-tejrama]

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:

1. Field Parameters : Let users pick the measures they care about. This alone cuts load time massively since only selected measures are calculated.
2. 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.
3. 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 Support

[v-tejrama]

Hi @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.

[v-tejrama]

Hi @elcamino ,

If this solution has resolved your query, kindly mark it as accepted.

Thank You.

[v-tejrama]

Hi @elcamino ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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).

[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!

[Elena_Kalina]

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

[MFelix · Accepted solution]

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
  Result

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

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

That said, 90 columns with 80 of those being measures is a lot of computation.