cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesf Frequent Visitor
Frequent Visitor

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
jamesf Frequent Visitor
Frequent Visitor

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.

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

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.
jamesf Frequent Visitor
Frequent Visitor

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.

jamesf Frequent Visitor
Frequent Visitor

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 127 members 1,706 guests
Please welcome our newest community members: