Mix Storage Mode Calculation

[kenneth0596]

Hi all,

I'm trying to calculate "Fee" by multiplying the actual hours from a direct query database by the billable rate from my import sharepoint. I added the following as the billable rate column:

IF(

   LOOKUPVALUE(SharePoint.RateCardorCostPlus], SharePoint[Project Name], RELATED(DirectQuery[Project Name])) = "Cost Plus",

   LOOKUPVALUE(SharePoint.Multiplier, SharePoint[Project Name], RELATED(DirectQuery[Project Name])) * LOOKUPVALUE(SharePoint[Cost Rate], SharePoint[EmployeeID], RELATED(DirectQuery[EmployeeCode])),

   LOOKUPVALUE(SharePoint[Rate], SharePoint[BusinessTitle], RELATED(DirectQuery[JobTitle]))

)

The logic is that if the project is a “Cost Plus” then we multiply employee cost rate by the client multiplier. If its not “Cost Plus” then we use our client rate card and look up the billable rate of the employee. When I add above column to the model, I receive this error message: “It cannot be pushed to the remote data source and cannot be used in this scenario”. Anyone knows how to fix this? I would really appreciate any insights you could provide. Thank you. 

[Anonymous · Accepted solution]

Hi @kenneth0596 ,

You may currently be in a limited model relationship. A model relationship is "limited" when there is no identified "one" side. For limited relationships, table expansion never occurs. You cannot use the RELATED DAX function to retrieve the "one" side column value. For more details, refer to the document: Model relationships in Power BI Desktop - Power BI | Microsoft Learn.

In addition, here is a solution similar to your problem, I hope it can help you.
Solved: Column cannot be pushed to the remote data source ... - Microsoft Fabric Community

Best Regards,

Clara Gong

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