Error with direct query ,result set over limit 1000000,join on service??

[CCCP]

Good morning
I have a situation where I am developing reports on pbi report builder and using dax studio for query creation and testing.
My model is in direct query.
I had to create custom queries in sql instead of importing the data because the database is not clean and perfect.
The problem I am experiencing is on the number of rows my queries return .

"The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’ rows."
Limit of 1000000 is easily exceeded and i don't know exactly why...
However, this error is strange to me because some tables do not have this numerosity.
Let me explain.
I query two tables ,company and fraud practices.
Company is linked  in inner join with the practices table which has about 1100000 rows .Company has 30 rows.
now if i create a query filtering for example a company i notice that from dax studio ,via server timings, two sql queries are created separately and sent separately on database (i suppose, i have oracle db).

one has a select on the company table with the filter i set.

(select company code,company description from company table where company code =10)
the other query, the heavier one goes on the practice table it is something like (
select field 1 ,2,3 from (select distinct ...from practices)
.
My question is :
Isn't the inner join that is set on the model ( I put the check mark on assume referential integrity) created on the database?are the two queries joined locally on the service ??why??is there a way to push this on the database side?
This would explain why the 1000000 rows exceeded error.
Is my consideration accurate?
can you tell my if it is feasible how to set up or try to do the join on the database instead of loading them and then putting them into join??!

notes:
I have a pro account on worskspace premium.
thanks and sorry for being verbose.

[CCCP]

HI Ibendin, i change my question , i would like to understand:

when i have a direct query model , the join in the model between two tables ,no matter the size of those, are pushed on the database??or there are sent two separate queries and merged locally on the service or on the  gateway itself?

thanks for your support and patience

[lbendlin]

This depends on a couple of factors. Best would be to use DAX Studio to see the actual query.

Regardless - one of the things you want to watch out for is the cardinality of the join field.

[CCCP]

HI ,yes the big table are around 1M and 3.5M rows.

But i would like to understand, how and where power bi computes the joins.

on server?or the join are pushed on database and after the result is loaded on the service?

thx

[lbendlin]

Read about the concept of Query Folding.  When you use handcrafted SQL you break query folding and make it your own responsibility to keep the results under the threshold. 

You have a couple of options - you could introduce a ROWNUM filter in your query, or you could consider switching to Import mode etc.

[lbendlin]

I had to create custom queries in sql 

Are any of these returning more than 1M rows?