Direct Query row limit in star schema

[twilliams98103]

I have a fact table with well over a million rows that I am connected to with Direct Query. I need to create a detail view that just shows rows from that table. When I add fields from the fact to a Table vizual, it works fine. However if I also drag on a field from any of my Dimensions it triggers the million row limit.

This is the DAX Studio query trace when I add two fields from the Fact Table ('CCR Cases'[Case Number] and 'CCR Cases'[Case Details]:

EVALUATE
  TOPN(
    501,
    SUMMARIZE('CCR Cases', 'CCR Cases'[Case Number], 'CCR Cases'[Case Details]),
    'CCR Cases'[Case Number],
    1,
    'CCR Cases'[Case Details],
    1
  )

ORDER BY
  'CCR Cases'[Case Number], 'CCR Cases'[Case Details]

Here is the query trace when I include a field from the dimension table ('UPC'[UPC Description])

EVALUATE
  TOPN(
    501,
    SELECTCOLUMNS(
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(
            SUMMARIZECOLUMNS(
              'CCR Cases'[Case Number],
              'UPC'[UPC Description],
              'CCR Cases'[Case Details],
              "CountRowsCCR_Cases", CALCULATE(COUNTROWS('CCR Cases'))
            )
          ),
          OR(
            OR(
              NOT(ISBLANK('CCR Cases'[Case Number])),
              NOT(ISBLANK('UPC'[UPC Description]))
            ),
            NOT(ISBLANK('CCR Cases'[Case Details]))
          )
        )
      ),
      "'CCR Cases'[Case Number]", 'CCR Cases'[Case Number],
      "'UPC'[UPC Description]", 'UPC'[UPC Description],
      "'CCR Cases'[Case Details]", 'CCR Cases'[Case Details]
    ),
    'CCR Cases'[Case Number],
    1,
    'UPC'[UPC Description],
    1,
    'CCR Cases'[Case Details],
    1
  )

ORDER BY
  'CCR Cases'[Case Number], 'UPC'[UPC Description], 'CCR Cases'[Case Details]

The obvious difference is the COUNTROWS expression in the second query. Is this what is triggering the million row limit? Is there a reason adding a field from a related table requires that?

[v-alq-msft]

Hi, @twilliams98103 

Based on my research, there is a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. The limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit even though the final result doesn't exceeds the limit.

Here are some suggestions for DircetQuery best practices.

DB design guidance

• Push calculated columns and measures to the source where possible. The closer to the source, the higher the likelihood of performance.
• Optimize! Understand the execution plans for your queries, add indices for commonly filtered columns, and so on.

Modeling guidance

• Start in the Power BI Desktop.
• Avoid complex queries in Query Editor.
• Don't use relative date filtering in the Query Editor.  
• Keep measures simple initially, and add complexity incrementally.
• Avoid relationships on calculated columns and unique identifier columns.
• Try setting “Assume Referential Integrity” on relationships – in many cases, this setting significantly improves query performance.  

General

• Apply filters first.
• Consider switching off interaction between visuals, which reduces the query load when users cross-highlight.
• Limit the number of visuals and the data per visuals, as described above.
• Enabling row-level security can result in large changes in performance. Be sure to test the different row-level security roles that your users will assume.
• There are query-level time-outs enforced by the service to ensure that long-running queries can't monopolize system resources. Queries that take longer than 225 seconds, time out and result in a visual-level error.

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Best Regards

Allan

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

[twilliams98103]

Hi @v-alq-msft ,

I am aware of the 1 million row limit. What I am trying to understand is why the 1 million row limit is not triggered when I use fields from a single table but is with fields from two related table

I included the DAX from the query trace hoping that at least someone could explain why the second query violates the 1 million row limit but the first does not.

thanks 

[amitchandak]

Dax functions like summarize and summarize columns may actually get that in power and calculate it.

Can you let us know what you are trying to achieve?

Also, refer Query Reduction

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

https://www.youtube.com/watch?v=4kVw0eaz5Ws

And mix mode :

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[twilliams98103]

I am simply trying to build a Table visual that displays row level detail. What I don't understand is why I can add as many fields as I want from my 4 million+ row fact table with no problem, but if I add just a single field from a joined dimension table to my table, I get the 1 million row limit error. What is it about adding a field from a joined table that causes the query to execute in such a way that it exceeds 1 million rows?

[ManuGP]

Hi! I also faced this issue. My conclusion was that the limit only applies when there are joins involved, if any of the individual query to each table is returning more than a million records before doing the join. And the workaround I found is to filter on fields of each table so that the 1 mill row limit is not exceeded.