Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Av1ator
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
v-yifanw-msft
Community Support
Community Support

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.

 

View solution in original post

2 REPLIES 2
v-yifanw-msft
Community Support
Community Support

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.

 

Hi, Ada.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.