Creating a relationship between tables from different databases

[Anonymous]

I have been using Power BI to create reports using tables from the same database, but now I need to link a table from another database. Either importing as a csv or connecting directly to the database would be acceptable. So far when I create a 1:1 relationship based on a unique key, it gives me the "Couldn't load the data for this visual. The resultset of a query to external data soruce has exceeded the maximum allowed size of '1000000' rows." when there should only be like 5 values, so I am clearly not doing it correctly.

Can someone explain how to do this?

Thank you!

[v-alq-msft]

Hi, @Anonymous 

There's 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. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, 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. It can also occur while building a visual, on the path to a more reasonable final state. 

I'd like to suggest you read the Guidance for using DirectQuery successfullly in the official document .

Best Regards

Allan

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

[Greg_Deckler]

This is Direct Query or import?

[Anonymous]

Both. AKA first I had everything done through a direct query but now I want to import a csv file and link it to one of the other tables.

[amitchandak]

Try to check queries running using sql profiler refer

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

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