Inner Join with Direct Query without pulling all data to client

[Anonymous]

Hi,

I'm using Direct Query to Oracle db

If I just add 1 table, PowerBi does not load all the data to client for the reports to work (applies filters and aggregations on report refresh).

However if I have 2 tables (A, B), joined via a unique key - K(a), K(b), and define a new merged query (inner join), then PowerBI tries to pre-load all the data to client (which does not work, millions of records).

The tables A, B container a lot of data and the reason for using Direct Query is performance. However PowerBi joins (merge query) seems to defeat the purpose of using Direct Query.

Am I missing something?

Note that I know I can handcraft my own join and work around the issue, I'm wondering whether there is a built-in way for joins to evaluate at report time (with filters and aggregations applied), rather then pre-fetch all the data?

Regards,

Stefan

[amitchandak]

@Anonymous , have you used +0 in measure or crossjoin.

Have you used bi-directional joins?

refer to this

https://guyinacube.com/2019/01/30/power-bi-query-reduction-when-using-directquery/

https://community.powerbi.com/t5/Desktop/Query-Reduction/td-p/964484

[Anonymous]

Hi @amitchandak ,

thank you for responding.

I'm not using any meassures or crossjoins. And DAX joins seem to only work on equivalent structure tables (not my case)

bi-directional joins seem to have no effect on merge query creation, likewise reducing query. They all seem to only have influence after the data is filtered.

Note that I can't even create the merged query because PowerBi seems to be overloaded with data.