Forum Discussion
URL Filter - Seeking help on IN filter
Hi all,
Apologies for yet another post regarding URL filtering - I've trawled through the forum and watched every YouTube video I can find.
Eventually, i've managed to get the following URL filter to work:
| https://app.powerbi.com/groups/XXX67XXXX/reports/XXX67XXXX-XXX67XXXX/ReportSection?filter=AutomationLibrary%2FTechnology%20eq%20%27KNIME%27 |
However, when I update the above URL to try and filter on the column Project Type for value 'Other' it now doesn't work (despite adding correct syntax between the words Project and Type.
**I've removed the initial URL path to make it easier to read :-)**
| ReportSection?&filter=AutomationLibrary%2FProject%20Type%20eq%20%27Other%27 |
Can anyone advise why this second URL filter may not work?
However, the above was to try and understand relevant syntax etc.
Now to my main intention. I actually need to filter the Table AutomationLibrary on column Project Type for values 'All Project Types' and 'Corporate Restructure'.
I've compiled my URL filter using the in syntax and it just won't work:
/ReportSection?&filter=AutomationLibrary%2FProject%20Type%20in%20(%27All%20Project%20Types%27,%27Corporate%20Restructure%27)
Please can anyone advise where my issue lies and why the URL filters above are not working?
Thanks in advance.
Pete
This page covers all of the URL filter encoding rules:
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters
Particular rules that are relevant for your situation:
- Spaces in table or column names must be encoded as _x0020_
This is why the Project Type filter wasn't working. - Spaces and other special characters in values must be encoded generally using %-code, e.g. space is %20.
(already appears correct in your example).
For your particular filter, this should be the correct filter portion of the URL:
filter=AutomationLibrary/Project_x0020_Type%20in%20(%27All%20Project%20Types%27,%27Corporate%20Restructure%27)I tested this with a dummy report myself and it worked as intended.
Does this work for you?
- Spaces in table or column names must be encoded as _x0020_
Hi taylorpeter55, no worries, good to hear from you!
I had a look through the URL filter documentation and there don't appear to be any substring-based filtering options. The 'in' filter just checks for an exact match with any of the items between the brackets.
While Power BI URL filters are based on OData filters, Power BI has a more limited set of options unfortunately.
I would instead recommend adjusting the semantic model so that you can use an 'in' filter on another specially constructed table. I have attached a PBIX as an example.
Here are the steps I would suggest:
1. Create a new table, which I called 'Project Type Concatenated'. This contains the distinct values from AutomationLibrary[Project Type] (each of which is a comma-delimited list):
2. Create another new table, which I called 'Project Type Individual'. This contains all combinations of the original Project Type Concatenated values and the individual Project Type values. I created it using the Splitting function in Power Query:
3. Create relationships as follows
(note the bidirectional relationship between 'Project Type Concatenated' and 'Project Type Individual') :4. Now, you can apply a filter on 'Project Type Individual'[Project Type], and this will have the effect of selecting any of the original Project Type column containing any of the selected individual Project Types:
5. For the URL filter, apply the 'in' filter to 'Project Type Individual'[Project Type]:
filter=Project_x0020_Type_x0020_Individual/Project_x0020_Type%20in%20(%27All%20Project%20Types%27,%27Corporate%20Restructure%27)Does something like this work for you?
Regards
4 Replies
This page covers all of the URL filter encoding rules:
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters
Particular rules that are relevant for your situation:
- Spaces in table or column names must be encoded as _x0020_
This is why the Project Type filter wasn't working. - Spaces and other special characters in values must be encoded generally using %-code, e.g. space is %20.
(already appears correct in your example).
For your particular filter, this should be the correct filter portion of the URL:
filter=AutomationLibrary/Project_x0020_Type%20in%20(%27All%20Project%20Types%27,%27Corporate%20Restructure%27)I tested this with a dummy report myself and it worked as intended.
Does this work for you?
OwenAuger Apologies for the delay in replying, I have been out of the office with no access to my laptop.
Thanks so much for your response, I really appreciate it.
I've tried your URL filter for 'All Project Types' and 'Corporate Restructure' and it correctly filters on rows with these categories called out individually:
However, I was hoping (rightly or wrongly) that the IN function would also filter on rows that contain one of the above values in addition to other categories.
Example:
Is the IN function capable of identifying these rows? Or is there a different function that I could use to include these rows?
Ideally, I would want to see all 5 rows in the above 2 screenshots included by the same URL filter (i.e. include any rows that contain the category rather than an exact match).
Thanks in advance for any further guidance you can provide.
Pete
- Spaces in table or column names must be encoded as _x0020_
Hi taylorpeter55, no worries, good to hear from you!
I had a look through the URL filter documentation and there don't appear to be any substring-based filtering options. The 'in' filter just checks for an exact match with any of the items between the brackets.
While Power BI URL filters are based on OData filters, Power BI has a more limited set of options unfortunately.
I would instead recommend adjusting the semantic model so that you can use an 'in' filter on another specially constructed table. I have attached a PBIX as an example.
Here are the steps I would suggest:
1. Create a new table, which I called 'Project Type Concatenated'. This contains the distinct values from AutomationLibrary[Project Type] (each of which is a comma-delimited list):
2. Create another new table, which I called 'Project Type Individual'. This contains all combinations of the original Project Type Concatenated values and the individual Project Type values. I created it using the Splitting function in Power Query:
3. Create relationships as follows
(note the bidirectional relationship between 'Project Type Concatenated' and 'Project Type Individual') :4. Now, you can apply a filter on 'Project Type Individual'[Project Type], and this will have the effect of selecting any of the original Project Type column containing any of the selected individual Project Types:
5. For the URL filter, apply the 'in' filter to 'Project Type Individual'[Project Type]:
filter=Project_x0020_Type_x0020_Individual/Project_x0020_Type%20in%20(%27All%20Project%20Types%27,%27Corporate%20Restructure%27)Does something like this work for you?
Regards
OwenAuger Thank you so much! This solution is brilliant 🙂
I've replicated it in my full dashboard and the dashboard and URL filter are now working perfectly 🙂
I honestly can't thank you enough - I will certainly remember this solution and will definitely be reusing it in future.Pete
