Forum Discussion

KeithCoxAuto's avatar
KeithCoxAuto
Helper I
5 years ago
Solved

Column cannot be pushed to the remote data source and cannot be used in this scenario

I'm just embracing composite data sets in a new role - there are 2 published data sets, one internal sales system data and one Dynamics CRM data.  Within the internal sales data, I need to multiply a net figure by the current tax rate, if applicable.

 

This is done with a calculated column: TEST= IF(RELATED(Item[TaxQualified])=TRUE,Sale[PaymentValue]*[taxrate Value],Sale[PaymentValue])

 

The column works fine but when I try to publish the combined model, I get the error message: 

Column[TEST] cannot be pushed to the remote data source and cannot be used in this scenario.

 

I have full permissions, read, reshare and build for both datasets.

 

Anyone experienced this error and know how to resolve it if it's not a permissions problem?

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi KeithCoxAuto ,

     

    Are you using DirectQuery for Power BI dataset and Import for another datasource? Based on my understanding, the RELATED function cannot be used to fetch a column across a limited relationship.

     

    Maybe you can try the measure:

    Measure = 
IF(
    SELECTEDVALUE('Item'[TaxQualified]) = TRUE,
    SUM(Sale[PaymentValue]) * SELECTEDVALUE('Item'[taxrate Value]),
    SUM(Sale[PaymentValue])
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    5 years ago

    Hi KeithCoxAuto ,

     

    Are you using DirectQuery for Power BI dataset and Import for another datasource? Based on my understanding, the RELATED function cannot be used to fetch a column across a limited relationship.

     

    Maybe you can try the measure:

    Measure = 
IF(
    SELECTEDVALUE('Item'[TaxQualified]) = TRUE,
    SUM(Sale[PaymentValue]) * SELECTEDVALUE('Item'[taxrate Value]),
    SUM(Sale[PaymentValue])
)

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

    • KeithCoxAuto's avatar
      KeithCoxAuto
      Helper I
      5 years ago

      Hi, thanks for the information on limited relationships, I hadn't realised that about composite models.

       

      Your measure does work - I just start to hit memory limits visualising a table with the full data set (only around 5k of rows currently) with more than a couple of other measures.

       

      Thanks very much for the solution

  • tanyapammy's avatar
    tanyapammy
    Frequent Visitor
    2 years ago

    Hi All, 

     

    I have similar issue. The datasource is a live connection to another dataset, and a dataflow 

    Power BI Model:

    We have a local model for the another dataset (aRUN)and with some calculated column with positive outcome.

    We have then added a dataflow as a secondary data source.

    We have then created a relationship between the article number (column Article) in the Dataflow table and the other dataset (column Group Article). (one to many, both files set as a text).

    Output: 

    Output:

    If we try to create a table, it is perfectly working and recognizing the relationship between dataset

    aRUN dataset and excel column.

    Problem

    WE have to create calculations using information coming from the two tables (such as a division, if statement and so on). We cannot do these calculations if the values are not stored in the same table, so we trying to do a VALUELOOKUP to add the column from the range list (and the other excel) into the aRUN dataset.

    For example, We tried to:

     

    Vlookup the MOQ from the Range List (that we will later use for an if statement with the arun quantity)

    Column = LOOKUPVALUE('Range List'[MinimumOrderQuantityArticle],'Range List'[Article],Arun[group_article])

     

    In every situation,  get this error:

     

     

     

    Is there any we we can overcome this limitation?

     

    Thanks,

    Tanya.