Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Did you hear? There's a new SQL AI Developer certification (DP-800). Start preparing now and be one of the first to get certified. Register now

GuyInACube
Microsoft Employee
‎03-21-2017 06:54 AM

Filter a report with a URL query string parameter

You may have seen some forum posts showing how to filter a report with a URL query string parameter. We also have this in the main documentation as well. It isn’t very widely known. So, I thought I’d do a blog post to highlight how to do it, and also give you a trick you can use to filter on multiple fields.

Filter on a field

Filters can be added to the query string of a report UR using the following syntax. This is based on OData $filter syntax. Only a string compare is available using eq, however.

?filter=<Table>/<Field> eq 'value'

Let’s assume that the URL to our report is the following.

https://app.powerbi.com/groups/me/reports/8d6e300b-696f-498e-b611-41ae03366851/ReportSection3

If we wanted to filter on a field called Territory in the Store table, our URL would now look like this.

https://app.powerbi.com/groups/me/reports/8d6e300b-696f-498e-b611-41ae03366851/ReportSection3?filter=Store/Territory eq 'NC'

There are a couple of things to be aware of when using this.

  • Field type has to be string
  • Table and field names cannot have any spaces
  • Table and field names are case sensitive. The value is not.
  • Fields that are hidden from report view can still be filtered

    image
  • Value has to be enclosed with single quotes
  • The field does not need to be present in the Filters pane. It can be used on any table/field within the model.

    SNAGHTMLbe2ceec
     

 

Filter on multiple fields

By default, you can only filter on a single field with the query string. You may have situations where you want to filter on multiple fields. One way you could do this is by creating a calculated column that concatenates two fields to a single value. Then we could filter on that item.

For example, say I have two fields called Territory and Chain.

SNAGHTMLbe68a6d

I can create a new Calculated column called TerritoryChain. Remember from above that I can’t have a space in the field name. It is ok for the value though. Here is the DAX formula for that column.

TerritoryChain = [Territory] & " - " & [Chain]

image

When that is deployed to the Power BI Service, I will have a URL like the following.

https://app.powerbi.com/groups/me/reports/8d6e300b-696f-498e-b611-41ae03366851/ReportSection3?filter=Store/TerritoryChain eq 'NC – Lindseys'

Using this URL with dashboard tiles

Say that we slice or filter our report, and then pin that visual to a dashboard. That slice, or filtering, isn’t saved with the report. So, when you select the tile, you will go into the report and not see it filtered the way the tile showed it. Enter the Custom URL of a tile! We can alter the URL of the tile with our filtered URL.

image

There may be some cases where this will be helpful from an end user experience when interacting between the dashboard and reports.

Labels:
0