cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

OData filter pass-through

I have found, through experimentation, that setting filters on properties annotated with Org.OData.Capabilities.V1.FilterRestrictions.NonFilterableProperties results in Power BI filtering the data locally, whereas filterable properties result in a "$filter=" query being passed to the source service. However, Power BI appears to ignore other FilterRestrictions annotations, such as RequiresFilter and RequiredProperties.

 

Is there any documentation or reference sample to explain the extent of annotation support for OData v4 services, particulary the Org.OData.Capabilities.*  and Org.OData.Core.* annotations?

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
New Member

Re: 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.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: OData filter pass-through

Hi @jamesf,

 

I got the information from PG that

 

"OData filter are not supported in Power BI

I know it’s working in PBI Desktop, but it will break on the service.

 

The solution is to Filter from the Power Query editor that generate a call to the function Table.SelectRows:

https://msdn.microsoft.com/en-us/library/mt260810.aspx

 

As to RequiresFilter and RequiredProperties, we don’t currently have any plans to support those. Please file a feature request at ideas.powerbi.com so that we can track interest."

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
New Member

Re: OData filter pass-through

Hi

 

Thanks for the reply!

 

I am not using OData filters explicitly (in the URL). I set filters in the UI and they are translated into Table.SelectRows(). The difference is that some filters are passed through to my OData service while others are not. This appears to be dicated by the NonFilterableProperties annotation, so Power BI must respect those annotations. I have verified that this behaviour works in the same way using both Power BI desktop and the service (via an enterprise gateway to my on-premises OData endpoint).

 

As an example for anyone who is interested, here is a snippet of the OData metadata describing my Documents entity set, with filter restrictions:

 

<EntitySet Name="Documents" EntityType="ns.Document">
  <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>Description</PropertyPath>
        </Collection>
      </PropertyValue>
    </Record>
  </Annotation>
</EntitySet>

 

If I filter Documents in the Power BI query editor (using the standard checkbox filter) on any property other than Description (which is annotated as non-filterable), the filter is passed through.

 

M:

let
    Source = OData.Feed("http://serviceurl/data/V1"),
    Documents_table = Source{[Name="Documents",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Documents_table, each ([VersionId] = 1))
in
    #"Filtered Rows"

 

 

Query recieved by my OData service:

/data/V1/Documents?$filter=VersionId eq 1

 

However, if I do the same for the non-filterable Description, I get the following.

 

M:

let
    Source = OData.Feed("http://serviceurl/data/V1"),
    Documents_table = Source{[Name="Documents",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Documents_table, each ([Description] = "Texas"))
in
    #"Filtered Rows"

 

Query recieved by my OData service - no filter specified:

/data/V1/Documents

 

You'll note that the M query is equivalent but the resulting OData query differs: in the second example, all the data will be retrieved and filtering will take place on the Power BI side. This is expected behaviour owing to the Org.OData.Capabilities.V1.FilterRestrictions annotation but is worth understanding, since the consequences of filtering on a non-filterable property can have a huge impact on the volume of data that is retrieved.

 

My question is whether there is any documentation or reference to indicate which annotations are supported and to what extent, given that some of the FilterRestrictions annotations appear to be respected while others are not. There are also similar annotations such as SortRestrictions, which I have not investigated. I was hoping to find the capabilities of the OData feed written down somewhere, to save me the trial and error Smiley Happy Does anything like that exist?

 

I will file a feature request for support of other annotations, as suggested.

 

Thanks for your time.

Highlighted
New Member

Re: 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.

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors