- Article
- 9 minutes to read
When you open a report in the Power BI service, each report page has its own unique URL. To filter this report page, you can use the Filter panel on the report canvas. Or you can add query string parameters to the URL to pre-filter the report. Maybe you have a report that you want to show your colleagues and you want to pre-filter it for them. One way to filter is to start with the report's default URL, add the filter parameters to the URL, and then email the full new URL.
This article uses the Retail Analytics sample report. If you want to participate, you canDownload sample report.
Used for query string parameters
Let's say you're working in Power BI Desktop. You want to create a report that links to other Power BI reports, but you only want to see some of the information in the other reports. First, filter the reports using query string parameters and save the URLs. Then create a table on the desktop with these new reporting URLs. Then publish and share the report.
Another use for query string parameters is for someone building an advanced Power BI solution. Using DAX, they create a report that dynamically generates a filtered report URL based on the selections made by the customer in the current report. When customers select the URL, they only see the information they wanted.
Query String Parameter Syntax for Filtering
Parameters allow you to filter the report by one or more values, even if those values contain spaces or special characters. The basic syntax is quite simple; Start with the report URL, add a question mark, and add your filter syntax.
URL?filter=Tisch/Zoneeq 'Wert'
- TischmiZoneNames are case sensitive,WertIt is not.
- Hidden fields in the report preview can still be filtered.
The field type
The field type can be a number, date/time, or string, and the type used must match the type specified in the dataset. For example, specifying a table column of type String will not work if you are looking for a datetime or numeric value in a record column specified as a date, for example "Table/StringColumn eq 1".
- Satanmust be enclosed in single quotes, as in "administrator name".
- countingThey do not require a special format. To seeNumeric data typesin this article for more details.
- dates and timesverdata date typesIn this article.
If you're still confused, read on and we'll break it down.
filter on a field
Let's say our report URL is as follows.
And we see in the map view above that we have stores in North Carolina.NORTH CAROLINAis the value that represents North Carolina inZoneField ofsave not computerDesk. To filter the report to only show bid data in "NC", we add this string to the URL:
?filter=store/territory eq 'NC'
Our report is now filtered for North Carolina; All visualizations in the report show data for North Carolina only.
Filter more than one value in a field
To filter more than one value in a single field, use thenooperator instead ofmioperator The syntax is:
URL?filter=Tisch/Zone no('valor1','Wert2')
In the same example, to filter the report to show only data for stores in "NC" (North Carolina) or "TN" (Tennessee), add the following to the URL:
?filter=store/territory in ('NC', 'TN')Watch theoperatorTable further down the article for a list of other useful operators.
Filter by multiple fields
You can also filter multiple fields by adding more parameters to your URL. Let's go back to our original filter parameter.
?filter=store/territory eq 'NC'To filter by more fields, add a "mi' and another field in the same format as above. Here is an example.
?filter=Store/Territory eq 'NC' and Store/Chain eq 'Fashions Direct'operator
Power BI supports many operators in addition to 'mi'. The following table lists these operators along with the type of content they support.
| Operator | Definition | line | number | Dice | example |
|---|---|---|---|---|---|
| mi | mi | mi | mi | mi | Product/price le 200 and price gt 3.5 |
| GL | same | mi | mi | mi | Address/City, eg 'Redmond' |
| that's | it is not the same | mi | mi | mi | Direction/Orth ne 'London' |
| age | better than or equal to | no | mi | mi | product/price ge 10 |
| gt | Larger than | no | mi | mi | Product/Price > 20 |
| Die | less than or equal to | no | mi | mi | Product/Price 100 |
| es | Smaller than | no | mi | mi | Product/price according to 20 |
| no* | Included | mi | mi | mi | Student/Alter at (27, 29) |
* When you useno, the values to the right ofnoit can be a comma-separated list enclosed in parentheses or a single expression that returns a collection. Watch theAT operator articlefor example.
Numeric data types
A Power BI URL filter can contain numbers in the following formats.
| number type | example |
|---|---|
| all | 5 |
| language | 5 liters or 5 liters |
| Doble | 5,5 o 55e-1 o 0,55e+1 o 5D o 5d o 0,5e1D o 0,5e1d o 5,5D o 5,5d o 55e-1D o 55e-1d |
| Decimal | 5m o 5m o 5,5m o 5,5m |
| float up | 5F o 5F o 0.5e1F o 0.5e-1d |
data date types
Power BI is compatible with OData V3 and V4 forDicemiDate and time offsettype of data. For OData V3, dates must be enclosed in single quotes and preceded by the word datetime. Single quotes and the word datetime are not required in OData V4.
Dates are represented in EDM format (2019-02-12T00:00:00): If you specify a date as "YYYY-MM-DD", Power BI interprets it as "YYYY-MM-DD00:00:00". Make sure the month and day have two digits, MM and DD.
Why is this distinction important? Suppose you create a query string parameterTable/Data gt '2018-08-03'. Will the results include August 3, 2018 or start August 4, 2018? Power BI translates your query toTable/Data gt '2018-08-03T00:00:00'. So your results will include all dates with a non-zero time part because those dates would be greater than'2018-08-03T00:00:00'.
There are other differences between V3 and V4. OData V3 does not support dates, only DateTime. So if you use the V3 format, you have to qualify it with the full date and time. Date literals such as "datetime'2019-05-20'" are not supported in the V3 notation. But you can write it as "2019-05-20" in V4 notation. Here are two equivalent filter queries in V3 and V4:
- OData V4 format: filtro=tabela/data gt 2019-05-20
- OData V3 format: filtro=tabela/data gt datahora'2019-05-20T00:00:00'
Special characters in URL filters
Special characters in table and column names
Special characters and spaces in table and column names require more formatting. If your query contains spaces, hyphens, or other non-ASCII characters, precede these special characters with aexhaust codestarting with an underscore and an X (_X), then the four digitsUnicode, then another underscore. If the Unicode is less than four characters, you must pad it with zeros. Here are some examples.
| identifier | Unicode | Coding for Power BI |
|---|---|---|
| table name | The space is 00x20 | Table_x0020_Name |
| Share@number | @ es 00x40 | column_x0040_number |
| [Share] | [es 0x005B] es 0x005D | x005B_Column_x005D |
| Column + More | + is 0x2B | Spalte_x002B_Plus |
Table_x0020_Name/Column_x002B_Plus eq 3
table_x0020_special/x005B_Column_x0020_Supports_x005Dequation '[C]'
Special characters in values
URL filters support most special characters in field values, but some require itescape codes. For example, to search for a single quote, use two single quotes ('').
?filter=table/name eq 'O''Brien'o:
?filter=table/name eq 'Lee's Summit'o:That
noThe operator also supports this escape:?filter=table/name in ('Lee's Summit', 'O'Brien')o:
Here is a list of some special characters that require escape codes in field values.
| Character | exhaust code |
|---|---|
| (a space) | % 20 |
| ' | '' |
| % | % 25 |
| + | %2B |
| / | %2F |
| ? | %3F |
| # | % 23 |
| & | % 26 |
Default URL Escape Characters
If you use a URL with spaces and other special characters, browsers may automatically replace them with standard escape characters. Suppose you create this URL query string:
https://app.powerbi.com/groups/me/reports/b7dea1d4-d9f0-47aa-a88d-xxxxxxxxxxxx/ReportSection2?filter=Executives/Executive eq 'Andrew Ma'
Open the customer profitability sample filtered by Andrew Ma. But if you look at the URL, it might look like this now:
https://app.powerbi.com/groups/me/reports/b7dea1d4-d9f0-47aa-a88d-xxxxxxxxxxxx/ReportSection2?filter=Executives%2FExecutive%20eq%20%27Andrew%20Ma%27
The browser replaced the space betweenandreasmiBreastcom% 20, as well as the other spaces. Replace the slash between table namesadministrationand the name of the fieldexecutivecom%2F, and replaced the single quotes'com% 27.
This version of a URL can be useful. For example, you can paste it into Microsoft Teams chat and it will return the filtered results you want.
Use DAX to filter multiple values
Another way to filter multiple fields is to create a calculated column that concatenates two fields into a single value. You can then filter by that value.
For example, we have two fields: Territory and String. On the Power BI desktop,Create a new calculated column(Field) named TerritoryChain. Remember thisZoneThe name cannot contain spaces. Here is the DAX formula for that column.
TerritoryChain = [territory] & " - " & [string]
Publish the report to the Power BI service, then use the URL query string to filter to display only the data for Lindseys NC stores.
https://app.powerbi.com/groups/me/reports/8d6e300b-696f-498e-b611-41ae03366851/ReportSection3?filter=Store/TerritoryChain eq 'NC – Lindseys'Freeze a block of a filtered report
After you filter the report using query string parameters, you can pin views of that report to your dashboard. The dashboard tile displays the filtered data, and selecting that dashboard tile opens the report that was used to create it. However, the filtering you performed using the URL is not saved with the report. Selecting the dashboard tile opens the report in its unfiltered state. As a result, the data displayed in the dashboard tile does not match the data displayed in the report preview.
This discrepancy is useful if you want to see different results. filtered on the dashboard and unfiltered on the report.
Considerations and troubleshooting
There are a few things to remember when using query string parameters.
- By using thenooperator, the values to the right ofnomust be a comma-separated list enclosed in parentheses.
- Power BI Report Server also supports the ability to specify more than one filter using the "filter" URL parameter. Here's an example of what the URL would look like in Power BI Report Server:
https://reportserver/reports/powerbi/Store Sales?rs:Embed=true&filter= Store/Territory eq 'NC' und Store/Chain eq 'Fashions Direct' - Report URL filters are limited to 10 expressions (10 filters connected by AND).
- The long data type is (2^53-1) due to JavaScript limitations.
- Power BI doesn't limit the number of characters in URL query strings. Different browsers have different length limitations.
- You cannot filter table or column names that start with capital lettersINF, including, for example, a table name beginning with "INFO". INF uppercase is a special value in OData. If you want to start a table or column name with "INF", type "inf" in lowercase.
onboarding scenarios
URL filters are supported in some embed scenarios and not in others.
- Embed a report in a secure portal or websiteis supported.
- URL filters are supported in Power BI Embedded. To seePower BI Embedded advanced URL filtering capabilitiesfor details.
- Query string filtering does not work withpublish on the weboExport as PDF.
- Embed with the Report web part in SharePoint Onlinedoes not support URL filters.
- Teams doesn't let you specify a URL.
Next steps
Pin a view to a dashboard
Sign up for a free trial
More questions?Try asking the Power BI community
FAQs
How do I filter a Power BI report using URL? ›
With parameters, you can filter the report for one or more values, even if those values contain spaces or special characters. The basic syntax is fairly straightforward; start with the report URL, add a question mark, and then add your filter syntax. Table and Field names are case-sensitive, value isn't.
How do you filter data in Power BI using parameters? ›- Open the desired power bi report you want to apply dynamic filtering (here we are using report for COVID data). ...
- The Future of Big Data. ...
- Step3: Make the country list unique. ...
- Step 4: Apply Parameterized filter to Table 1 table, so data load only for selected country.
- Determine which filter parameter number you want to use.
- Find the words or numbers that represent the field values by which you want to filter.
- Add a filter URL parameter to the url argument in spud code and/or to a URL-encoded query string.
The PHP Server object provides access to the query string for a page URL. Add the following code to retrieve it: $query_string = $_SERVER['QUERY_STRING']; This code stores the query string in a variable, having retrieved it from the Server object.
Can we use parameters as filters? ›Parameters are extremely simple – they allow users to select or input a value. That value can then in turn be used in calculations, and subsequently, filters. You can even set the values that users can select to be automatically created from a field in your data, which saves time.
How do you use filter parameters? ›- Right-click in the data pane and select Create Parameter....
- In the dialog box, name the parameter. ...
- Under Data type select String.
- Under Allowable values select List.
- Click Add from Field > Region.
- Add an additional value of All with the display text of All.
- Click OK.
The filter function takes two parameters: Function: Function that tests if elements of an iterable are True or false. If none, the function defaults to identity function returning false if any elements are false. Iterable: Iterable which is to be filtered could be sets, tuples, lists or containers of any iterators.
How do you access parameters in a URL? ›- By using the URLSearchParams Object.
- By using Separating and accessing each parameter pair.
URL filtering is a way of “blocking” certain URLs from loading on a company network. If an employee would attempt to visit this URL, either by entering it manually or clicking a link in a search engine, they will be redirected to a page notifying them that this content is blocked.
How do you specify parameters in a URL? ›To add a parameter to the URL, add a /#/? to the end, followed by the parameter name, an equal sign (=), and the value of the parameter. You can add multiple parameters by including an ampersand (&) between each one.
Does URL include query string? ›
A query string is a part of a uniform resource locator (URL) that assigns values to specified parameters.
What are query string parameters in URL? ›What are query string parameters? Query string parameters are extensions of a website's base Uniform Resource Locator (URL) loaded by a web browser or client application. Originally query strings were used to record the content of an HTML form or web form on a given page.
Does URL path include query string? ›Query string is always a part of the URL. Parameters can be buried in form-data datastream when using POST method so they may not appear in the URL.
What are the 3 basic method of filtering? ›There are several filtration methods : simple or gravity, hot and vacuum filtrations. The selection of the appropriate method is typically dictated by the nature of the experimental situation.
How do you add interactive filters to a report? ›To create a new interactive filter, type “interactive filters” into the navigator and select the module that appears under the Reports application menu (in the Administration grouping). This will present you with a list of existing filters.
What is the difference between using a filter and a query to find records? ›Queries are slower it returns a calculated score of how well a document matches the query. Filters are faster because they check only if the document matched or not. Queries produce non-boolean values. Filters produce boolean values.
Can you use the filter method on a string or? ›You can't use filter() on a string as it is an Array. prototype function.
When can a filter condition be parameterized? ›2.4 Parametrization of Filter condition
The filter condition could be completely parameterized in filter transformation by creating an Input parameter of type expression for which value could be passed during the mapping runtime or from mapping task.
– Add an “&” symbol between each new item you are passing. – The first item should start with a “?”, but if there is already a question mark symbol in the URL string, use an ampersand instead (the question mark should only be used once per URL).
How do you apply a value filter to a field? ›To filter by values, select <Field name> > Values Filters and then create a values filter. To filter by specific row labels, select Filter, uncheck Select All, and then select the check boxes next to the items you want to show. You can also filter by entering text in the Search box. Select OK.
What are the parameters or methods which classify filters? ›
Filters can be placed in one of two categories: passive or active. Passive filters include only passive components—resistors, capacitors, and inductors. In contrast, active filters use active components, such as op-amps, in addition to resistors and capacitors, but not inductors.
How do I filter data in DAX? ›You do this by specifying a table as an input to the formula, and then defining a filter expression. The filter expression you provide is used to query the data and return only a subset of the source data.
How do you make filter actions work for Measure names using parameters? ›- Create a new worksheet Bar Chart.
- Drag Order Date to Columns.
- Drag Measure Names to Filters card, select Sales and Profit.
- Drag Measure Values to Rows.
- Drag Measure Names to the Color of Marks Card.
- Change the chart type to Bar.