topic Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook? in Data Engineering

How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

liney_marcela — Mon, 17 Mar 2025 21:41:57 GMT

Hi, I am using a PySpark Notebook to export a Power BI report in PDF format via the API. I managed to export it with page-level filters and some filters applied via code. However, I cannot apply a date filter during the export process.

Here is the code I am using:

import requests import json page_name = "xxxx" apply_filters = True url = f"https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/reports/{reportId}/ExportTo" payload = { "format": "PDF", "powerBIReportConfiguration": { "pages": [ { "pageName": page_name, } ] } } if apply_filters: payload["powerBIReportConfiguration"]["reportLevelFilters"] = [ { "filter": "CU_00x_xx/Columna_Fecha eq '2025-12-01'" } ] headers = { "Content-Type": "application/json", "Authorization": f"Bearer {access_token}" } response = requests.post(url, headers=headers, data=json.dumps(payload)) if response.status_code == 202: exportId = response.json().get("id") print(f"Export started. ID: {exportId}") else: print(f"Export error: {response.status_code} - {response.text}")

The issue is that the date filter is not applied correctly in the exported report. Am I using the correct format for the date in reportLevelFilters? Should I use a different syntax or structure for the API to recognize it?

Any help or examples on how to properly apply a date filter in this case would be greatly appreciated.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

lbendlin — Mon, 17 Mar 2025 22:10:44 GMT

if you use the V3 format, you must qualify it with the full date time. Date literals like "datetime'2019-05-20'" aren't supported in V3 notation. But you can just write it as "2019-05-20" in V4 notation. Here are two equivalent filter queries in V3 and V4:

OData V4 format: filter=Table/Date gt 2019-05-20
OData V3 format: filter=Table/Date gt datetime'2019-05-20T00:00:00'

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

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

v-saisrao-msft — Wed, 19 Mar 2025 05:25:05 GMT

Hi liney_marcela,
I wanted to check if you had the opportunity to review the information provided by lbendlin. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

liney_marcela — Wed, 19 Mar 2025 21:54:04 GMT

Hi lbendlin

Thank you for your response! I really appreciate your help.

I have tested both formats you mentioned:
- OData V4: `filter=Table/Date gt 2019-05-20`
- OData V3: `filter=Table/Date gt datetime'2019-05-20T00:00:00'`

Unfortunately, I was still unable to filter by date in my report. Other filters (such as text or numerical values) work correctly, but the date filter doesn’t seem to be applied.

Would you have any additional suggestions on what might be causing this issue?

Thanks again for your time!

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

v-saisrao-msft — Sun, 23 Mar 2025 14:45:42 GMT

Hi liney_marcela,

Thank you for reaching out to the Microsoft Fabric Forum Community.

It seems you're encountering difficulties with applying a date filter when exporting your Power BI report using the REST API. Since text and numerical filters are functioning correctly, the problem likely lies in the filter syntax, the column’s data type, or the way the report processes the filter.

Here are a few suggestions to help troubleshoot and resolve the issue:

To apply a date filter using the Power BI REST API, make sure you're using the correct OData V4 syntax. For Date type columns, a simple format like YYYY-MM-DD should work. For DateTime columns, you need to provide the full date-time value in the YYYY-MM-DDT00:00:00 format. Unlike OData V3, V4 doesn’t require a datetime prefix, simplifying the filtering process.
Make sure the Columna_Fecha column in your Power BI report is set as a Date or DateTime type. If it's stored as Text or String, the API won't recognize the date filter. You can check and update the data type using Power BI Desktop in the Data view or the Modeling tab.
Ensure that the table and column names in your API request match the actual names in your Power BI dataset. If the names contain spaces or special characters, enclose them in single quotes. You can verify the correct names using the Fields pane in Power BI Desktop.
If your export request succeeds but the filter isn’t applied, use the Get Export to File Status API endpoint to check detailed information about the export status. It can indicate whether the filter was applied or ignored. Also, confirm that your API token has the necessary permissions to apply report-level filters.

Please refer to the links below for more information:

Reports - Export To File - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Reports - Export To File In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

v-saisrao-msft — Wed, 26 Mar 2025 04:31:28 GMT

Hi liney_marcela,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

v-saisrao-msft — Sat, 29 Mar 2025 15:55:01 GMT

Hi liney_marcela ,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

liney_marcela — Mon, 31 Mar 2025 16:44:15 GMT

Hi,

Thank you for your response. However, I have already validated the reason why the date filter is not applied, and it is because it only works for old dates like 19XX. Therefore, I have already made a suggestion regarding this restriction.

Re: How to Apply a Date Filter When Exporting a Power BI Report via API in a PySpark Notebook?

v-saisrao-msft — Thu, 03 Apr 2025 04:37:34 GMT

Hi liney_marcela,

Thank you for sharing your findings on the date filter behavior in the Power BI API. It's helpful to know that the filter works correctly for older dates but not for recent ones. This seems to be a system limitation, and your feedback on this issue is valuable.

If you have already submitted a suggestion regarding this, it will help with potential improvements in future updates.

Please consider accepting your response as a solution to assist other community members facing a similar issue.

Thank you.