Direct Query relationship to table having more than 1M rows

[Dmitry]

Is it possible to add Direct Query relationship between tables if one of them has more than 1M rows?

I have 2 tables in the same SQL DB: one table has 2 rows, another table has 1,1M rows. Whenever I try to add a new relationship between these 2 tables, Power BI Desktop become non-responsive (tried single and both directions). It fails only for Direct Query, works fine when using Import. 

Power BI Desktop Version: 2.70.5494.661 64-bit (juni 2019)

[v-piga-msft · Accepted solution]

Hi @Dmitry ,

I'm afraid that your scenario hit the limitation for direct query mode.

Please have a good look at this document to know more about the limitations for direct query.

• Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
• There is a one-million-row limit for returning data when using DirectQuery. The limit does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

Best  Regards,

Cherry