Skip to main content
Microsoft Idea

Power BI

Needs Votes

OData.Feed should push predicates on navigation properties to URL

Vote (25) Share
Stanislaw Swierc's profile image

Stanislaw Swierc on 07 Oct 2016 07:19:23

Currently when you specify filters (Table.SelectRows) on navigation property the predicate does not get pushed to the URL. PowerBI Desktop downloads all the data locally and then does filtering client-side. This is very inefficient. It would be great if OData.Feed was smart enough to push predicates on navigation properties to URLs.

Here is example of my script:
let
Source = OData.Feed("https://account.analytics.visualstudio.com/_odata"),
WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data],
#"Expanded Area" = Table.ExpandRecordColumn(WorkItems_table, "Area", {"AreaPath"}, {"AreaPath"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Area", each ([AreaPath] = "Fabrikam Finance"))
in
#"Filtered Rows"

When I run it I see the following request to the server which has correct $expand clause but has *no* $filter clause.

https://account.analytics.visualstudio.com/_odata/WorkItems?$expand=Area($select=AreaSK,AreaPath)

Since I'm filtering on AreaPath I would expect OData.Feed to be smart enough to use the following URL:

https://account.analytics.visualstudio.com/_odata/WorkItems?$expand=Area($select=AreaSK,AreaPath)&$filter=Area/AreaPath eq 'Fabrikam Finance'

Comments (4)
Stanislaw Swierc's profile image Profile Picture

Paul W on 05 Jul 2020 23:56:25

RE: OData.Feed should push predicates on navigation properties to URL

This thread is 3 years old yet seems to describe the current 2019 situation as far as I can tell (warning, I am still new to PowerBI and only "sense" this situation via PBI performance). Can someone estimate when there will be assistance for pushing filter (and join) criteria to the server so only desired data is returned rather than returning everything for PBI to post-process? Plus, any help in joining two+ Odata services in an efficient way, or must this always be done on PBI side?

Stanislaw Swierc's profile image Profile Picture

John Madden on 05 Jul 2020 23:13:00

RE: OData.Feed should push predicates on navigation properties to URL

Also true with Filters on DateTime columns. For example using the 'After' filter on a DateTime column creates a proper ODATA query with a Filter specified. However, using a 'In the Previous' x days filter PowerBI creates a ODATA query with absolutely no Filter. So it retrieves all data for all time and then Filters inside of PowerBI instead of at the Server.

Stanislaw Swierc's profile image Profile Picture

Trevor Carnahan on 05 Jul 2020 22:31:54

RE: OData.Feed should push predicates on navigation properties to URL

Critical to make odata viable.

Stanislaw Swierc's profile image Profile Picture

Paul Chapman on 05 Jul 2020 22:31:52

RE: OData.Feed should push predicates on navigation properties to URL

This would speed up queries across larger amounts of data.