Forum Discussion
Apply filters on REST API JSON POST request
hello,
I'm trying to call the powerBI REST api (JSON)
i can get the page i want and creat the pdf, but i cant' manage to apply filters,
I've created a really simple page for tests (just a slicer and a table).
this code get me the page but filters aren't applied:
the pdf i got:
the pdf i want:
Ok i've figured why i had this problem, my segment was set to "one selection" so there was always an item selected, the problem was the api downloaded the report with this selcection, then tried to filter it. i just had to uncheck "only one selection", uncheck everything, save the report, then i could filter my report!
19 Replies
Try the same but with a table visual, not a slicer.
First, thx a lot for the response (and sorry for my bad english) but i don't get it,
should the filter be active on both anyway?
i tried dozens of lines i remember one with "reportlevelfilters" i never managed to get rid of the errors and another one with "visuallevelfilters" i tried both on the slicer and on the table but, as the first one i posted here goes to the end without errors, give me the pdf, but filter arent applied.pdf still exactly the same.
I'm totally new about JSON, first script ever. it's hard to pass the "error..." (took me several days....) but now its seems to go through correctly...but nothing, i don't even know where to look to know where goes the rest of my command.In your report page you have a slicer visual. These behave different from other visuals when it comes to filtering. Try the same process but switch the visual type from slicer to table.
Hi,lbendlin ,thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.
Hello,Jdrien .I am glad to help you.
Here's how I tested your code.{ "format": "PDF", "powerBIReportConfiguration": { "page": [ { "pageName": "8e0816bfd0860b75b040" } ], "filter": { "$schema": "http://www.powerbi.com/product/schema#basic", "target": { "table": "data_refreshTest", "column": "animal" }, "operator": "eq", "values": ["dog"], "filterType": 1 } } }According to your description, you mentioned that the API commands can be executed normally, but the filters don't seem to work.
According to the parameter list on the official website, there is a filters parameter in the request, so I think there may be a problem with the content of the request parameter (i.e. the content of the filters) in the body of your request. The filters were not successfully passed into the api, causing this problem.
In the request, you need to pay attention to the various escapes in the request code to make sure that the parameters are passed correctly, and that there are no duplicates or missing symbols.
API:
Reports - Export To File
Reports - Export To File - REST API (Power BI Power BI REST APIs) | Microsoft Learn
About the parameters in report
URL:
Filter a report using query string parameters in the URL - Power BI | Microsoft Learn
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello! thx for this answer!
i managed to pass the filter on the testpage,
this code:{"format": "PDF","powerBIReportConfiguration":{"pages": [ { "pageName": "bfed89c6a60971412909"}],"reportLevelFilters": [{"filter": "AuditPdf/associe eq 95"}]
}}
does the trick, guess the way i wrote 95 was my mistake...
i now have a filtered pdf.
But I'm afraid i'm not done yet, the real page i need to filter got tablenames and column names with special caracters, i understand thats why it didnt worked as intended.
Table: Default_1 filtre Gauche
Column: Associé.
....how do i have to encode this to make it work?
Ok i've figured why i had this problem, my segment was set to "one selection" so there was always an item selected, the problem was the api downloaded the report with this selcection, then tried to filter it. i just had to uncheck "only one selection", uncheck everything, save the report, then i could filter my report!
- Hello, nice people. I have been reading this post, and it seems to look like the one that is related to the problem I am facing now. I have been trying to export a PowerBI report to a PNG image, but when I apply a filter before exporting it does not work,my body looks like this.const body = {format: "PNG",powerBIReportConfiguration: {formatSettings: { OutputFormat: "PNG" },settings: {locale: "en_US",},pages: [{pageName: "ReportSection1",},],reportLevelFilters: [{target: {table: "test1group and company",column: "company",},operator: "eq",values: ["5"],filterType: 1,},],//reportLevelFilters: ["test1group and company/company eq '5'"], // i also used this way
//filter: ["test1group and company/company eq '5'"], // i also used this way
identities: [{username: "email",roles: ["S"],datasets: ["datasetId"],},],},};i tried so many options but my filter is not working at all. if anyone has an idea of how i can work around this issue kindly help,i also followed documentation
https://learn.microsoft.com/zh-cn/rest/api/power-bi/reports/export-to-file#code-try-0
https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filterswhen i checked on the link above i saw filter receives a string as for the example belowi tried to stringfy the object but still get the same issue and passing something like this "test1group and company/company eq '5'"
if anyone can help i will be appreciative it
thanks
You need to escape spaces in table and column names
Hallo lbendlin
thank you for getting back to me,
I have different scenarios
1st scenario.
reportLevelFilters: [
{
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "test1group%20and%20company",
column: "company",
},
filterType: 1,
operator: "eq",
values: ["5"],
requireSingleSelection: false,
},
],i get this kind of errorPower BI Export Error: {
code: 'InvalidRequest',
message: 'Export report filter with empty string provided'
}2nd scenario is when i use paramaters
reportLevelFilters: [
{ filter: "test1group%20and%20company%2Fcompany%20eq%20'5'" },
],this doesn't show an error, the image is exported but without filter applied
3rd scenario
reportLevelFilters: [
{
filter: {
$schema: "http://powerbi.com/product/schema#basic",
target: {
table: "test1group%20and%20company",
column: "company",
},
filterType: 1,
operator: "eq",
values: ["5"],
requireSingleSelection: false,
},
},
],when i add filter the response change to this
Power BI Export Error: {
code: 'BadRequest',
message: 'Bad Request',
details: [
{
message: 'Invalid value',
target: 'request.powerBIReportConfiguration.reportLevelFilters[0].filter'
},
{ message: "'request' is a required parameter", target: 'request' }
]
}i am really stuck on this topic, every resources that i can find is suggesting the same thing
i followed this table for escaping the all required characters
if you have ever got this before or anyone here was in the same situation as mine, kindly help out
thanks
Faby
Hi lbendlin and everyone
I have a question, is there anywhere that I can exclude some visuals from being filtered,
for example,
let's say: BOB has 5 restaurent [1,2,3,4,5]Restaurant income booking 1 (Nov)1000 Nov(5),Dec(7) 2 (Nov):500 Nov(2),Dec(3) 3 Nov:400 Nov(1),Dec(2) 4 Nov:3000 Nov(45),Dec(10) 5 Nov: 2000 Nov(7),Dec(7)
BOB needs to export Restaurant 1 in November, but for booking, he wants to see ALL(Oct, Dec).income values show 1000, but for bookings, instead of 5, it shows 12.
so using the reportLevelFilter, the filter affects all visuals therefore I was wondering if there is a way to exclude some visuals.Thank you in advance for the one who can show me the way out
regards
Fab
I don't think mixing report level filters and visual interactions is a good idea. But go ahead, try it out.
Hallo lbendlin do you by chance know how i can find documentation for visual interaction. i tried using
pages: [{pageName: "ReportSection9",visuals: [{visualName: "f986f3cfa280c2986e82",interactions: [{value: "M",},],},],
This is not working
also somthing like thisbut i seem to be wrong, do you have any idea of how i can go around thispages: [{pageName: "ReportSection9",visuals: [{visualName: "f986f3cfa280c2986e82",filters: [{filter: `tablename/culumn eq 'M'`,overrideDefaultFilters: true,},],},],},],
thanks
It worked for me, your booking column should be a measure like
booking = CALCULATE(sum(....), FILTER(table, table[month]=selectedvalues(month))
and u can set a segment to select month, call 2 filters in your export, restaurant and month, u should be able to select more than one month.
