Can we have functionality like web application in Power BI?

[AnkitSingla]

As per the requirement, user should be provided with a text box and Search button on the report.

On the text box, user is supposed to enter an email.

Based upon the user input, it should trigger Power Shell script which in turn fetches the result from Azure SQL database.

Those results need to be displayed in the report.

This is a kind of web app functionality.

Can it be done in Power BI?

I tried using Dynamic M Query parameter. it is not supported for Azure SQL.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considerat...

[nandukrishnavs]

Hi @AnkitSingla ,

It could be achieve using PowerApp custom visual. The search box and button should be created in the PowerApp. Using PowerAutomate you can invoke PowerShell script with the help of AzureFunction. Once you have the result data, you can temporarily save it in Azure SQL along with your login id (login id is required for RLS). When you pass a new email address in the PowerApp, you can replace the existing result. This table needs to be connected to PowerBI using DirectQuery mode. With the help of RLS, you will see only your query results from the temporary table.

[AnkitSingla]

Hi @v-yiruan-msft ,

Parameter works in Power Query the way you have shared.

However, as per the requirement, user will provide input on Power BI report. This input will go to Azure function as parameter. Azure function has power shell script in it which returns JSON response. This response needs to be parsed and displayed in power BI report.

This complete flow is like a web app. However, it needs to be done using Power BI.

[v-yiruan-msft]

Hi @AnkitSingla ,

I just tried to apply query parameter in the sql statement from SQL Server database, it works just as shown in below screenshot. Could you please validate whether it can also work in Azure SQL?

Power BI Desktop Query Parameters

How do I pass parameters to my SQL statement?

Best Regards