Table Visual unable to load data despite having fewer rows.

[SaiK]

Hi,
I have two table visuals in a report on the same page in power bi desktop. I have applied pagination to both the tables with the display of items set to 25. Each table displays 25 items on each page. In total I have data of size 3 crore rows for the first table which is working fine.

Similarly am doing the same thing for the other table which is having data of size 47lac rows. But this visual is not working, it is saying query resources limit reached. Whereas the other table is working fine despite having more data. What is the possible cause of this issue and how to go about this?

I am importing the tables from a MySQL db with maria db connector.

[MFelix]

Hi @SaiK ,

This can be caused by several different things mainly the queries that you are sending to the engine that may be reaching the limit as @Bibiano_Geraldo said. Without additional information is difficult to understand what is happenning.

One option can be to remove fields from the 2nd table until you get a result that way you may understand what is happening and try to focus on that specific values or set of values.

Without any specific details is difficult to pin point the root causes.

[Bibiano_Geraldo]

Hi @SaiK ,

Probably, the second table contains some DAX calculations that are consuming a lot of resources. I recommend carefully reviewing the calculations in that table to identify any potential performance issues.

Additionally, you can use the Performance Analyzer to assess the table's performance and determine which queries or measures are impacting efficiency.

[SaiK]

Thanks for the prompt response. But this is very weird considering both the tables are using different parameters and the rest all features are same, despite having less data the table is not rendering. Shall try this out and update you about the same.

[Bibiano_Geraldo]

I understand, that does seem strange, especially if the tables are using different parameters and the rest of the settings are similar. The fact that one table isn't rendering, even with less data, could be due to another factor like the complexity of the calculations or filters applied.

Try using the Performance Analyzer to identify where the issue might be, and if the calculations are correct, it might also be worth checking for any issues with the table indexes or relationships. Let me know how it goes!

[SaiK]

Hi,
The DAX query is taking 225934ms for this table. But the query for both the tables is the same. Another thing is as soon as I remove the pagination filter from the table, it is rendering fine. The pagination filter(which restricts the amount of items displayed on the page). Also the table is rendering the data if I limit the amount of rows to top 5000. 

[Bibiano_Geraldo]

Hi,

It looks like the pagination filter is impacting performance. Since removing it allows the table to render and limiting rows to 5000 works, the issue might be how the filter interacts with the dataset. Even if the DAX query is the same, Power BI could be handling rendering differently due to filters. Try checking how the pagination filter is applied, whether it adds extra processing, and if the query can be optimized. Running the Performance Analyzer while toggling the filter might give more insights. Let me know what you find!

[SaiK]

The pagination filter is seperate for both the tables. The Table A filter is working fine and rendering the data. Whereas the Table B filter is causing issue becasue if I remove the filter, then the entire data is displayed without the top N 5000 filter too. In case of pagination for the Table B it isworking only if the TOP N is set to not more than 5000

[Bibiano_Geraldo]

There's way to share no sensitive file? To see closer what causes this loss in performance

[SaiK]

It's tough because it has sensitive company data. But for reference this is the video I referred to build the pagination in my table. UX Revolution with Visual Level PAGINATION in Power BI

[Bibiano_Geraldo]

Hi @SaiK ,
Please share the generic Dax's used on tha table

[SaiK]

DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"180 Days"}, 'slicer'[Values])

  VAR __DS0FilterTable2 = 
    TREATAS({25}, 'Items'[Items])

  VAR __DS0FilterTable3 = 
    TREATAS({1}, 'Pages'[Pages])

  VAR __ValueFilterDM0 = 
    FILTER(
      KEEPFILTERS(
        SUMMARIZECOLUMNS(
          'TableB'[Column A],
          __DS0FilterTable,
          __DS0FilterTable2,
          __DS0FilterTable3,
          "Measure A", 'Table B'[TMeasure A],

          "Item_Filter", IGNORE('Table B'[Item Filter])
        )
      ),
      [Item_Filter] = 1
    )

  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'Table B'[Column A],
      __DS0FilterTable,
      __DS0FilterTable2,
      __DS0FilterTable3,
      __ValueFilterDM0,
      "Measure A", 'Table B'[Measure A]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(501, __DS0Core, [Measure A], 0, 'Table B'[Column A], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [Measure A] DESC, 'Table B'[Column A]

[Bibiano_Geraldo]

Hi @SaiK ,

try to update your DAX to look like bellow, this avoid using summirecolumnns twice and improve the perfomance:

DEFINE
    VAR __FilteredTableB =
        CALCULATETABLE(
            'Table B',
            TREATAS({"180 Days"}, 'slicer'[Values]),
            TREATAS({25}, 'Items'[Items]),
            TREATAS({1}, 'Pages'[Pages]),
            'Table B'[Item Filter] = 1
        )

    VAR __DS0Core =
        SUMMARIZE(
            __FilteredTableB,
            'Table B'[Column A],
            "Measure A", 'Table B'[Measure A]
        )

    VAR __DS0PrimaryWindowed =
        TOPN(501, __DS0Core, [Measure A], DESC, 'Table B'[Column A], ASC)

EVALUATE
    __DS0PrimaryWindowed

ORDER BY
    [Measure A] DESC, 'Table B'[Column A] ASC

[SaiK]

Query (8, 13) A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Got this error

[Bibiano_Geraldo]

Sorry about that, use filter instead:

DEFINE
    VAR __FilteredTableB =
        CALCULATETABLE(
            'Table B',
            TREATAS({"180 Days"}, 'slicer'[Values]),
            TREATAS({25}, 'Items'[Items]),
            TREATAS({1}, 'Pages'[Pages]),
            FILTER('Table B', 'Table B'[Item Filter] = 1)  // ✅ Fixed filter
        )

    VAR __DS0Core =
        SUMMARIZE(
            __FilteredTableB,
            'Table B'[Column A],
            "Measure A", 'Table B'[Measure A]
        )

    VAR __DS0PrimaryWindowed =
        TOPN(501, __DS0Core, [Measure A], DESC, 'Table B'[Column A], ASC)

EVALUATE
    __DS0PrimaryWindowed

ORDER BY
    [Measure A] DESC, 'Table B'[Column A] ASC

[SaiK]

Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1024 MB, memory limit 1024 MB.

[SaiK]

@danextian @MFelix and others out there, please help out at the earliest.