OData filter pass-through

For the sake of completeness and in the absence of any formal reference material, I have done a bit more testing to confirm that both the NonFilterableProperties and NonSortableProperties annotations are respected when Power BI queries OData sources. If properties of the OData entity sets are marked with these annotations, then Power BI will bring back all the data and perform the filtering and / or sorting locally rather than passing through $filter and / or $orderby query options to the source.

It is worth noting that if some of the properties involved in a filtered are annotated as non-filterable, then the whole filter will be applied locally. You can account for this by restructuring your queries to perform filtering in multiple steps.

Here is a complete example using a simple entity call with 2 identical properties, Prop1 and Prop2. Prop1 has been annotated as both non-filterable and non-sortable. This appears in the metadata as:

<EntitySet Name="Projects" EntityType="sample.Project">
  <Annotation Term="Org.OData.Capabilities.V1.FilterRestrictions">
    <Record>
      <PropertyValue Property="Filterable" Bool="true"/>
      <PropertyValue Property="RequiresFilter" Bool="true"/>
      <PropertyValue Property="RequiredProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="NonFilterableProperties">
        <Collection>
          <PropertyPath>Prop1</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
  <Annotation Term="Org.OData.Capabilities.V1.SortRestrictions">
    <Record>
      <PropertyValue Property="Sortable" Bool="true"/>
      <PropertyValue Property="AscendingOnlyProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="DescendingOnlyProperties">
        <Collection/>
      </PropertyValue>
      <PropertyValue Property="NonSortableProperties">
        <Collection>
          <PropertyPath>Prop1</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
</EntitySet>

Querying the filterable, sortable property yields the following Power BI query and resultant OData request:

let
    Source = OData.Feed("http://myserver/Data/V1"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Projects_table, each ([Prop2] = "Proj1" or [Prop2] = "Proj2" or [Prop2] = "Proj3")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Prop2", Order.Descending}})
in
    #"Sorted Rows"

http://myserver/Data/V1/Projects?$filter=Prop2 eq 'Proj1' or Prop2 eq 'Proj2' or Prop2 eq 'Proj3'&$orderby=Prop2 desc

On the other hand, querying the non-filterable, non-sortable property yields the following Power BI query and resultant OData request:

let
    Source = OData.Feed("http://myserver/Data/V1"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Projects_table, each ([Prop1] = "Proj1" or [Prop1] = "Proj2" or [Prop1] = "Proj3")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Prop1", Order.Descending}})
in
    #"Sorted Rows"

http://myserver/Data/V1/Projects

Note that the query (M code) is the same but the resultant OData URL is quite different. In the second case, Power BI is requesting the entire Projects entity set so that it can perform the filtering and sorting on the returned data.

To the end user, the outcome of these queries would be identical but, if the entity set was very large, the time taken to load the data could be much longer in the second case.

This behaviour is expected, since Power BI respects the NonFilterableProperties and NonSortableProperties annotations.

With this knowledge, it is possible to structure your queries in such a way that you make the most of the native OData query options that are passed through to the source.