Direct Query to PBI Dataset...calculation help needed

[codyraptor]

I have a Power BI Dataset live connection and I'm trying to join another datasource to it.  The only field I have available in common is an ID that would require a many to many.  When I try to combine values from the Dataset and the new source, I get an error showing it hitting the 1Million limit.  It seems it's pushing the calculation back to the dataset through DQ rather than calculating it locally.  Below is a simple idea of the model....Calculations are simple...just sums of orders and claims.  Any help would be appreciated.

Category joins Summary by Cat-Key (one ot many)

Summary joins Orders by ID (many to many)

Want to show sum of orders by Category

[v-jingzhang]

Hi @codyraptor 

Which table is the DirectQuery data source and which table is the new data source? If you only add Sum of orders into a visual, will it return the same "hitting the 1Million limit" error? Did you add sum of orders and sum of claims into the same visual?

In addition, how many category items are in the visual? You can try filtering the category items to a small subset to test if this will have the same error. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[codyraptor]

@v-jingzhang 
Category and Summary are both in a live DQ PBI dataset.  Orders are in a local import file.  I can sum orders and everything works fine because it's only hitting the local model.  I can also pull over anything from Category and Summary.  It's only when I place something from Orders and Category in the same table that it slows down drastically and gives the error.  I have the Category filtered to only 1...doesn't really matter when I limit the data...it seems as if it's having to pull everything over again in order to make the calculation.