Power BI now offers enhanced filtering capabilities that let you define more complex report-level filters as query string parameters directly in the report URL, as the following screenshot illustrates. This example shows the Retail Analysis sample with a report-level filter to display results only for the item category โ050-Shoesโ, for the year 2014 (the last year with data in this sample solution), and only for new stores in the sales territories of Kentucky and Ohio. You can see the applied report-level filter in the Filters pane.
The following query string defines this filter definition. It demonstrates that you can filter on multiple fields and different data types and illustrates how to escape column names that have special characters. The sequence _x0020_ escapes a space to specify the column name โStore Typeโ in OData-compliant syntax. The filter definition also shows how to use the in operator to narrow down the sales territory.
?filter=Time/Date ge 2014-01-01 and Time/Date lt 2015-01-01 and Store/Store_x0020_Type eq ‘New Store’ and Item/Category eq ‘050-Shoes’ and Store/Territory in (‘KY’, ‘OH’)
Power BI Report URL filters follow the OData syntax to be consistent with the filtering methods in other Power BI areas (such as, filter statements in Power BI cmdlets), but not all OData filter capabilities are supported in Report URL filters. For a good overview of supported operations, see the article Filter a report using query string parameters in the URL in the product documentation.
Report URL filters can come in handy if you want to create shortcuts to pre-filtered reports. In Windows, for instance, you could create Desktop shortcuts for quick and easy access to the information you care about most. You could also add the URLs to the Favorites list of your Web browser or use them as custom URLs with dashboard tiles to view the filtered report directly from a tile. Another option is to create a table in a Power BI solution with the desired report URLs, as explained in the article Hyperlinks in tables in the product documentation.
It is even possible to construct the filter URL dynamically in a Power BI solution by using a DAX measure, as in the screenshot below. This DAX expression checks if the user applied a filter on the Store/Territory column, such as by using a slicer, and generates the filter URL using the available values if this is the case.
Of course, the above is a simplistic example, but it illustrates the possibilities to generate filtered report URLs dynamically. For your reference, hereโs the DAX expression to implement this measure.
filteredURL = IF(ISFILTERED(Store[Territory]), “https://app.powerbi.com/groups/me/reports/beb3e658-8372-4bfe-8cb2-914287c75b62/ReportSection3?filter=Store/Territory in (‘” & CONCATENATEX(VALUES(Store[Territory]), Store[Territory], “‘,'”) & “‘)”)
And thatโs it for a quick introduction of the enhanced URL filtering capabilities that you can use in your Power BI solutions. For additional information, read the Filter a report using query string parameters in the URL article in the product documentation, and stay tuned for more filtering improvements coming soon.