dynamically pass parameter to Power bi Report in PBI Report service

[Anonymous]

Dear Support Team,

I am running one power bi service where i have all the reports present.

Many of the reports have more than one dropdown filters in each report, In the inital load of report all the items in dropdown is selected and based on that data populates in report.

Now here the requirement is i have to specify a filter parameter based on that only that should populated in dropdown and based on it report should load.

For that purpose i did some R &D and found like using querystring we can achieve the same. I tried the below for the 1st Filter like below:

https://app.powerbi.com/groups/somegroupid/somereportid/a9317228-85a5-4cf7-8d9c-f14af41b82fe/ReportS... eq 'some value'

Below i have few query related to the url parameters:

1. I tried above link with passed querystring here is tablename/fieldname, in field name i pass value for the first filter and found like it is working.

2.  Now the second filter is related to the same table with another column.

For that i tried like below:

https://app.powerbi.com/groups/somegroupid/somereportid/a9317228-85a5-4cf7-8d9c-f14af41b82fe/ReportS... eq 'some value' and MyTable1/MyField2 eq 'some value'

When i tried like above found like neither for the first filter parameters results is not working nor for the second parameter filter data is loading.

3. In some of my table i have some other schema defined in that case how would i use that table name in parameters?

e.g: [myschema].[mytable]

4. Some of the table name have space, Even same is also for the column field also, In that case how will use the same.

5. We know that there is some text limitation in Url, In that case if we have to pass more values which could go beyond of the limitation, Do we have any other option to do for the same?

Please suggest.

[v-lili6-msft]

hi  @Anonymous 

Here is a same post, you could refer to it:

https://community.powerbi.com/t5/Service/URL-query-string-parameters-on-multiple-table-and-fields/td-p/901719

Regards,

Lin

[Anonymous]

Hi CST, 

Here my requirement is slightly changed. Now instead passing parameter through url i have to pass the filter value through embed code only.

What is my requirement and what i have done till now.

Data design in Report: In my report i have one master dropdown with some item listed. 

If i check or uncheck some item in the list based on that report data is filtering and displaying.

Currently in the filter dropdown all the records are displaying but actually this filter items should display based on the userid.

To do that i have added one more column called userid to the master filter related table.

And updated one userid to one of the filteritem.

Now in the embed code i used the filter code like below:

 const filterDataByUser = {
                        $schema: "http://powerbi.com/product/schema#basic",
                        target: {
                            table: "MyTable",
                            column: "UserID"
                        },
                        operator: "In",
                        values: ['174']
                    };
 var config = {
                        type: 'report',
                        tokenType: models.TokenType.Embed,
                        accessToken: aadPBIToken,
                        embedUrl: aadPBIEmbdUrl,
                        id: aadPBIRptID,
                        permissions: models.Permissions.All,
                        settings: {
                            filterPaneEnabled: true,
                            navContentPaneEnabled: true,
                            //customLayout: {
                            //    displayOption: models.DisplayOption.FitToPage
                            //}
                        },
                        //pageName: defaultPage,
                        filters: [filterDataByUser]
                    };
 var reportContainer = $('#reportContainer')[0];
                    var report = powerbi.embed(reportContainer, config);

As you can see in above code for the table same userid which i updated in database set here also.

But still in the embedded report filtered data is not populating in the dropdown.

Please let me know, If you have  any concern.

Please suggest.

[Anonymous]

HI Team,

Any solution on above asked?

Thanks,

Vikash

[Anonymous]

HI All,

I have found like the table and column i used to filter the data that contains Underscore. Once i renamed both and removed the underscore and found like filtering data is working fine.

Thank you to all of your support.

[nandukrishnavs]

@Anonymous 

Refer to this https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters

You can find full documentation.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂