composite model added column does not work

[vijaykaali811]

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. 

[v-saisrao-msft · Accepted solution]

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. 

[v-saisrao-msft]

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.

[v-saisrao-msft · Accepted solution]

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. 

[vijaykaali811]

it is working fine but it has limitation of 150,000 rows overcome it 

[v-saisrao-msft]

Hi @vijaykaali811,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @vijaykaali811,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-saisrao-msft · Accepted solution]

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.