Dynamics 365 Related Table Filtering

[Mark_Holtham]

Hi,

I've started to move existing report queries from "Tenancy/XRMServices/2011/Organization.svc" to  OData 9.0 and finding that existing related table filtering is not working. I suspect it is a syntax issue but cannot find a workable solution online.

In my example I am trying to get order lines relating to a specific data in the order header. There are is a custom date field I normally reference which differs from the main created date but this does not work either

Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01

Any chance someone can advise me on URL filtering for this scenario?

[v-yuezhe-msft]

@Mark_Holtham,

This issue is more related to Dynamics 365, please post the question in Dynamics 365 forum to get correct OData URL, then use it in Power BI Desktop.


Regards,
Lydia

[Mark_Holtham]

A Microsoft engineer asked me to transition to the API 9.0 URL. Can you claify which one I should be using?

I have posted in the other forum but I'm sure other users would appreciate a solution here.  The Power BI forums is a great resource point.

[Mark_Holtham · Accepted solution]

It seems that I have hit a bit of a limitation on the current implementation of REST API 9.  The other forum didnt quite help but kindly presented enough information for me to find an alternative.

Instead of using a $filtering, I have referenced an existing system view in Dynamics 365. I know the report is at risk of any changes to the system view, but the approach offloads the query to Dynamics, which then is pulled in to Power BI.

let
Source = OData.Feed("https://<tenant>.api.crm4.dynamics.com/api/data/v9.0/invoices?savedQuery=00000000-0000-0000-0000-000000000000", null, [Implementation="2.0"])

in

Source

The savedqueryid was found in the savedqueries table.

I hope this can be of use to someone.

[v-yuezhe-msft]

@Mark_Holtham,

Glad to here the issue is solved, you can accept your reply as answer to close this thread.

Regards,
Lydia