Composite Model - Calculated column in import table that sums data from related direct query table

[Av1ator]

Good day, Community. 

Curious if this is possible, or maybe I am looking at something the wrong way, but in my composite model I am trying to create a calculated column in my import table that sums up a column from my direct query table. When I try this I am getting a "OLE DB or ODBC error." Import table is one-to-many to my direct query table. 

I know I can create a measure for this, but I need to try to test this out for a certain project. Is this just not doable right now in composite models? I tried researching as best I could but could not find a reason why this is not doable. 

Thank you!

[v-yifanw-msft · Accepted solution]

Hi @Av1ator ,

Based on the information you have provided, it is possible to efficiently create and use calculated columns in a composite model. However, there are specific limitations when these calculations span different source groups, such as between DirectQuery and imported data. The errors you encounter are usually due to these limitations.

In a composite model, although you can create relationships between tables in the DirectQuery source and imported data, it can be challenging to attempt calculations that aggregate or summarise data from these source types because the DirectQuery source handles and stores data differently from imported data.

Creating measures is the recommended method for aggregating data from different source groups in a composite model. Measures are dynamically calculated at query time, allowing for aggregation to be handled across DirectQuery and imported data. If possible, consider specifying calculations either directly in the source system of the DirectQuery table or during the data import process of the importing table. This way, the calculation becomes part of the data model without crossing source group boundaries.

For more information about composite models and their limitations, we recommend that you read the following documentation:

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

Composite model guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Av1ator]

Hi, Ada.

Thank you for this detailed information and breakdown. Noted. Thank you again for your time.