Basic INNER/LEFT joins in Data Model

Hi rnola16 Try pushing the aggregations and everything to database here are some of the tips please try these out

Push Joins and Aggregations to the Database

• Use a custom SQL query in DirectQuery mode:

SELECT A.MEMBER_NBR,
       SUM(CASE WHEN A.AID_ID IN ('1', '2') THEN 1 ELSE 0 END) AS Aid_Count,
       COUNT(DISTINCT C.SSN) AS Unique_SSN,
       SUM(B.AMOUNT) AS Total_Amount
FROM A
INNER JOIN B ON A.REC_ID = B.REC_ID
INNER JOIN C ON A.SSN = C.SSN
WHERE A.MEMBER_NBR IN ('parameter1') AND B.SVC_DT >= 'parameter2'
GROUP BY A.MEMBER_NBR

• This minimizes data movement and uses the database engine for performance.

Avoid Power Query Joins for Large Tables

• Let the database handle joins instead of using Power BI's "Merge Queries" feature, which struggles with billions of rows.

Use Pre-Aggregated Data

• Preprocess and import only aggregated results to reduce data size.

DirectQuery Best Practices

• Set up relationships and avoid row-by-row calculations.
• Use parameters to filter data at the source.
  If this post helped please do give a kudos and accept this as a solution
  Thanks In Advance

Great points, Akash_Varuna  I completely agree that pushing joins and aggregations to the database is the best approach.

rnola16 Additionally, I'd suggest ensuring query folding is happening in Power Query to maximize performance. You can check this by right-clicking a step in Power Query → 'View Native Query.' If it's grayed out, Power BI is handling the computation instead of the database, which can cause performance issues.

Another optimization is using partitioned tables in the database if working with billions of rows, as this will help reduce query execution time.

- Optimize DirectQuery Models

https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance 

- Query Folding in Power Query

https://learn.microsoft.com/en-us/power-query/query-folding-basics

If this solution was helpful, please accept it as a solution or give kudos to help other community members.