Composite model issue

Hi _power_bi -Especially when dealing with large datasets and DirectQuery. The key points to consider are how the query is being executed, the caching behavior of Power BI, and the modeling setup. With DirectQuery, it’s essential to ensure that the query is being folded correctly. Query folding refers to the process of translating Power BI actions into SQL queries that can be executed by the data source. If the query isn’t fully folded, Power BI might retrieve more data than necessary and then apply filters locally, which can be very slow.

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Composite Model - Relationships: Ensure that the relationships between the customers, customer_orders, and order details tables are correctly set up. The order details table should have a direct relationship with customer_orders, typically using the OrderID. Misconfigured relationships can lead to inefficient queries.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

If your current approach isn't optimizing the DirectQuery as expected, using Dynamic M Query Parameters could help. This technique allows parameters to be passed directly to the source query, which can significantly improve performance.

Hope the above resources and information helps.