User-Interactive OData Requests in Power BI Desktop

[JanSir]

I am trying to create an interactive dashboard in Power BI where users can dynamically change the OData request using a slicer or other GUI element.  However, I am facing an issue where I am unable to use dynamic parameters because OData is not a DirectQuery source. What is the best way to create OData requests in Power BI that allow users to interactively change the displayed data sets without preloading all available data?

[Adamboer]

One potential solution to this issue is to use a combination of Power Query and Power BI's "Edit Queries" feature.

First, create a Power Query in Excel or Power BI Desktop that connects to your OData source and filters the data based on your desired parameters. Then, load this filtered data into your report.

Next, open the "Edit Queries" window in Power BI and add a parameter to represent the user's desired filter value. This parameter can be set up to update dynamically based on the user's selections in a slicer or other GUI element.

Finally, adjust your original Power Query to reference the parameter added in the "Edit Queries" window. This will allow the filtered data to be updated dynamically based on the user's selections, without the need for a DirectQuery connection.

Overall, this approach allows for dynamic user interaction with OData requests while still maintaining efficient data loading.

[Adamboer]

One potential solution to this issue is to use a combination of Power Query and Power BI's "Edit Queries" feature. First, create a Power Query in Excel or Power BI Desktop that connects to your OData source and filters the data based on your desired parameters. Then, load this filtered data into your report.

Next, open the "Edit Queries" window in Power BI and add a parameter to represent the user's desired filter value. This parameter can be set up to update dynamically based on the user's selections in a slicer or other GUI element.

Finally, adjust your original Power Query to reference the parameter added in the "Edit Queries" window. This will allow the filtered data to be updated dynamically based on the user's selections, without the need for a DirectQuery connection.

Overall, this approach allows for dynamic user interaction with OData requests while still maintaining efficient data loading. Let us know if you have any further queries or if there is anything else we can assist you with.

Next, open the "Edit Queries" window in Power BI and add a parameter to represent the user's desired filter value. This parameter can be set up to update dynamically based on the user's selections in a slicer or other GUI element.

Finally, adjust your original Power Query to reference the parameter added in the "Edit Queries" window. This will allow the filtered data to be updated dynamically based on the user's selections, without the need for a DirectQuery connection.

Overall, this approach allows for dynamic user interaction with OData requests while still maintaining efficient data loading. Let us know if you have any further queries or if there is anything else we can assist you with.

[JanSir]

Thank you for this suggestion. Can you elaborate more on the part "This parameter can be set up to update dynamically based on the user's selections in a slicer or other GUI element."? This is a crucial part, and it seems to me that without DirectQuery, this is impossible. 

[Anonymous]

HI @JanSir,

You can take a look at the following document to create a parameter to affect the backend query tables and bind with table fields to use on data view side with filters.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[JanSir]

Thank you for this suggestion, but it does not provide a solution. In the prerequisites is written: "To work through these procedures, you must have a valid M query that uses one or more DirectQuery tables.". OData is not a DirectQuery source.

[Anonymous]

Hi @JanSir,

Perhaps you can refer to the following link about create custom connector to expand and add directquery mode support to it if helps:

Enabling DirectQuery for an ODBC-based Power Query connector - Power Query | Microsoft Learn
Regards,

Xiaoxin Sheng

[JanSir]

Hi @Anonymous 

Yes, the ODBC connector shall be a possible solution. However, it is not convenient in our case to implement the ODBC connector to our dataset. 

We conclude that there is no straightforward way of resolving this issue with our current setup. 

Thanks, 

Jan