Just Analytics Blog | Performance Management News, Views and Op-ed

Filtering in Power BI

Written by Shwetank Sheel | Apr 11, 2017 3:31:27 AM

You are now able to pass filters to a PowerBI report using the URL. This is exciting news, because this now allows us to pass context from any other report / external link to a Power BI report. It's a feature I've been awaiting for a long time, and am glad this is now available, albeit with a few limitaions.

Let's first address HOW to do this, and we'll come back around to the considerations. To begin with, you need to get the URL of the report:

The structure of filter query URL will be as below to apply a filter.

While this feature is still under enhancement - at this time, there are some limitations that you need to be cognizant of:

  • The field type has to be a string / text.
  • The Table and Field names cannot have spaces. Using Quotes or HTML characters hasn't worked for us so far.
  • The Table and Field names are case sensitive. However, the value is NOT case sensitive.
  • Only equal (eq) is supported at this time.
  • Only 1 field can be passed as a filter.
  • The value must be enclosed in single quotes.

There are some other things to consider:

  • The field itself can be hidden from repot view.
  • The field DOES NOT need to be in the filter pane.
  • While you can only filter on 1 field - if you actually need to pass a more complex filter (using multiple columns), you can create a new calculated column using DAX to join the different columns you need to filter on, and use that in the URL.

 

When operationalizing this, you will likely want to make it dynamic, rather than hardcoding the value to be filtered on. This is quite easy to do by creating a new column using DAX to embed the value for each row, as below:

This column can then be exposed in a table or matrix. Just remember to mark the column as a "Web URL" so PowerBI automatically makes it clickable.

Additionally, in the visuals, please enable the "URL icon" so it displays an icon instead of the long long long long URL.

 

If you're interested in knowing more about this, or in understanding how to get PowerBI and SQL Azure to work for you, please click below to reach out to us.