Forum Discussion
composite model added column does not work
I have access read only dataset . i have imported my table from my excel .
Trying to add a column in shared dataset , but it says "Column cannot be pushed to the remote data source and cannot be used in this scenario" . how can i achieve this . as i cannot add measure it need to be column.
Hi vijaykaali811,
Thank you for reaching out to the Microsoft Fabric Forum Community.
when working with a Power BI composite model that combines a shared dataset (which is likely in a read-only state) with an imported Excel table, and you’re encountering the error message when trying to add a calculated column to the shared dataset. This error typically occurs when you're trying to add a calculated column in a way that the remote data source does not support, particularly when working with datasets that are read-only or shared.
You cannot add a calculated column directly to a read-only shared dataset in this scenario. However, you can create a new calculated table in your Power BI Desktop report that replicates the shared dataset’s table and includes your desired column. This new table will be stored locally in Import mode, allowing you to define the column’s logic freely, such as performing calculations or combining data from the Excel table. In Power BI Desktop, you can create this table using the Fields pane, then hide the original shared dataset table to ensure your visuals use the new table. Next, establish relationships between the new table and your Excel table or other dataset tables using common keys.
If your column’s logic can be computed outside Power BI and is based solely on your Excel data, you can add the column to your Excel table before importing it. For example, use Excel formulas to calculate the values, then import the updated table and create a relationship with the shared dataset using a shared key.
For more details, check these Microsoft Learn articles and Power BI Community threads:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Solved: Column cannot be pushed to the remote data source ... - Microsoft Fabric Community
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi vijaykaali811,
It is a known limitation when using a composite model with DirectQuery.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
6 Replies
Hi vijaykaali811,
Thank you for reaching out to the Microsoft Fabric Forum Community.
when working with a Power BI composite model that combines a shared dataset (which is likely in a read-only state) with an imported Excel table, and you’re encountering the error message when trying to add a calculated column to the shared dataset. This error typically occurs when you're trying to add a calculated column in a way that the remote data source does not support, particularly when working with datasets that are read-only or shared.
You cannot add a calculated column directly to a read-only shared dataset in this scenario. However, you can create a new calculated table in your Power BI Desktop report that replicates the shared dataset’s table and includes your desired column. This new table will be stored locally in Import mode, allowing you to define the column’s logic freely, such as performing calculations or combining data from the Excel table. In Power BI Desktop, you can create this table using the Fields pane, then hide the original shared dataset table to ensure your visuals use the new table. Next, establish relationships between the new table and your Excel table or other dataset tables using common keys.
If your column’s logic can be computed outside Power BI and is based solely on your Excel data, you can add the column to your Excel table before importing it. For example, use Excel formulas to calculate the values, then import the updated table and create a relationship with the shared dataset using a shared key.
For more details, check these Microsoft Learn articles and Power BI Community threads:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Solved: Column cannot be pushed to the remote data source ... - Microsoft Fabric Community
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
it is working fine but it has limitation of 150,000 rows overcome it
Hi vijaykaali811,
It is a known limitation when using a composite model with DirectQuery.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi vijaykaali811,
We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.
Thank you.
