Apply filters on REST API JSON POST request

[Jdrien]

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:

[fabyx]

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

[Jdrien]

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.

[lbendlin]

I don't think mixing report level filters and visual interactions is a good idea. But go ahead, try it out.

[fabyx]

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 this

pages: [

{

pageName: "ReportSection9",

visuals: [

{

visualName: "f986f3cfa280c2986e82",

filters: [

{

filter: `tablename/culumn eq 'M'`,

overrideDefaultFilters: true,

},

],

},

],

},

],

but i seem to be wrong, do you have any idea of how i can go around this 

thanks

[fabyx]

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: [

{

$schema: "http://powerbi.com/product/schema#basic",

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-filters

https://learn.microsoft.com/en-us/rest/api/power-bi/reports/export-to-file-in-group

when i checked on the link above i saw filter receives a string as for the example below

i 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

[lbendlin]

You need to escape spaces in table and column names

[fabyx]

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 error 

Power 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

[lbendlin]

that's not how you escape spaces.

Filter a report using query string parameters in the URL - Power BI | Microsoft Learn

[fabyx]

@lbendlin thank you for the help it worked 

[Jdrien · Accepted solution]

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!
 

[v-jtian-msft]

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.

[Jdrien]

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?

[lbendlin]

Try the same but with a table visual, not a slicer.

[Jdrien]

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.

[lbendlin]

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.

[Jdrien]

I did it, nothing seems to be selected now:

but this is only a test page, the original page i have to turn into filtered pdf got 3 slicer visuals and i cant replace them with tables.
there has to be a way to manipulate the slicers!

[lbendlin]

So the filter did not work on the table visual? You will want to fix that first.

[Jdrien]

thx, i managed to get the filter working on the test page with :

{

    "format": "PDF",

    "powerBIReportConfiguration": {

"pages": 

[

{

        "pageName": "bfed89c6a60971412909"  

      }

],

        "reportLevelFilters": [

            {

                "filter": "AuditPdf/associe eq 95"

            }

        ]

    }

}
but i have trouble appliing this to the main page cause the table and column have specials caracters:
table name is "Default_1 filtre Gauche"
column is "Associé"
i tried like this:

{

    "format": "PDF",

    "powerBIReportConfiguration": {

"pages": 

[

{

        "pageName": "bfed89c6a60971412909"  

      }

],

        "reportLevelFilters": [

            {

                "filter": "Default_1 filtre Gauche/Associé eq 95"

            }

        ]

    }

}

but it don't work, i think i have to encode those caracters but i don't know how.

[lbendlin]

Please follow the documentation

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters