We have the ability to pass filters to Power BI reports so we can contextually filter the data displayed. This is useful when we are integrating Power BI with 3rd party applications and embedding in web pages where we want to show filtered data. Here are the steps to do this.
First, connect to data. We will use the sample Accounts data here, which can be connected to via the Power BI Web connector.
Rename Table 0 to Accounts:
Next, create a report with visualizations. The visualizations will show unfiltered data, i.e. all accounts, all industries etc:
Now, publish the report to the Power BI Service:
We see the report now in the Power BI Service:
Our report URL is: https://app.powerbi.com/groups/me/reports/02ff8888-a5fd-4dc7-c9dabf5ca0a5/ReportSection.
Now let’s add the filters. To do this, we append to our report URL: ?filter=query/field eq ‘data to filter on’
For example, let’s say we want our report to only show data for AccountId 7. Our query would look like:
https://app.powerbi.com/groups/me/reports/02ff8888-a5fd-4dc7-c9dabf5ca0a5/ReportSection?filter=Accounts/AccountId eq 7
We can see the data on the report has changed, and it has picked up the filter:
Or if we wanted to filter by City = ‘Oklahoma City’, we can do:
https://app.powerbi.com/groups/me/reports/02ff8888-a5fd-4dc7-c9dabf5ca0a5/ReportSection?filter=Accounts/City eq ‘Oklahoma City’
As mentioned, this is useful for embedding scenarios. For more information on operators and data types, check out https://docs.microsoft.com/en-us/power-bi/service-url-filters.
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
Hello Carl,
Is it possible to pass a dynamic parameter instead? 😀
Hi Aya, I think it depends how you plan on opening the page. You could construct the URL dynamically depending on your situation.
Hi carl, i was able to follow with you until the end, great tutorial. Problem is, the filter applied by the URL is show in the filter pane, in my case i MUST hide this due to information security but i would like to hide only this filter created by the URL since my filter pane contains other business filters.
I stumbled upon a property that hides the entire filter pane, but i couldn’t find one to hide only this URL filter. Any ideas?
Did you Find out any solution yet? My scenario is similar to your’s.
Hi, did you find solution to above problem. I am going through similar situation at my end. Need workaround.. Please help. Thanks
dynamicParameterURL =
var rpturl=”https://app.powerbi.com/groups/guidid?filter=”
var rptfilter= “Applications/App_Key eq &SELECTEDVALUE(Applications[App_Key],MIN(Applications[App_Key])) —this is your dynamic filter
return
rpturl&rptfilter
Hi Christopher,
Is it possible to have dynamic dates as parameter filter ? I need to fetch the data for previous 2 years and I am currently using static value in my url like below
Source = OData.Feed(CRM & “/accounts?$filter=createdon gt 2019-01-01″, null, Implementation=”2.0”]),
But I would like to have the date dynamically to always fetch from the start of last 2 yrs. Any ideas on how to fetch it ?
Thanks in advance
Hi Carl, Is it possible to have dynamics dates as parameter filter ? I need to fetch the data for previous 2 years and I am currently using static value in my url like below
Source = OData.Feed(CRM & “/accounts?$filter=createdon gt 2019-01-01″, null, Implementation=”2.0”]),
But I would like to have the date dynamically to always fetch from the start of last 2 yrs. Any ideas on how to fetch it ?
Thanks in advance.
Hi carl,
When we pass the filter through URL to powerBi dashboard, Can we actually show up this selection in the slicer in the report.
example: imagine i have report with some visuals and slicer in it. so when I’m filtering the visaul for the city = “Oklahoma” , I need this value to be shown in the the slicer as well.
Thanks in advance!
Carl,
I follow your and it works my question if we make it dynamic like ?Accounts/Color eq Values(Accounts[Color]) or something like that so the end users doesn’t have to enter new values every times he/she access another report.
Thanks