Dynamic M Query Parameters Work in PostgreSQL but Not in SQL Server on Power BI Report Server

[VarunDC]

Hi Community,

I'm encountering an issue with M query parameters in Power BI Report Server. The setup works perfectly with PostgreSQL and report server and also functions correctly on Power BI Desktop for report server when using SQL Server. However, when I publish the report to Power BI Report Server, it fails to work.

Details:

• Environment: Power BI Report Server Jan 2024 and SQL Server 15.0.2000
• Issue: Dynamic M query parameters work fine in PostgreSQL and on Power BI Desktop with SQL Server, but not when published to Power BI Report Server.
• Error Message: No specific error message, but the parameters do not function as expected on the Report Server.

  Troubleshooting Steps Taken:

  1. Checked Permissions: Verified that the user account used by Power BI Report Server has the necessary permissions on the SQL Server database.
  2. Tested on Power BI Desktop: Confirmed that the dynamic M query parameters work as expected on Power BI Desktop with SQL Server.
  3. Verified Data Source Settings: Ensured the connection string and credentials in the data source settings are correct and consistent with the setup on Power BI Desktop.

     Additional Information:

     • The dynamic M query parameters work perfectly with PostgreSQL in both Power BI Desktop and Power BI Report Server.
     • The issue only arises when using SQL Server and publishing to Power BI Report Server.

       Example of Dynamic M Query:

       Here's an example of the dynamic M query I'm using:

       let
       ParameterValue = #"Parameter Name",
       Query = "SELECT * FROM TableName WHERE ColumnName = '" & ParameterValue & "'",
       Source = Sql.Database("ServerName", "DatabaseName", [Query = Query])
       in
       Source

       Question:

       Has anyone faced a similar issue or can provide insights on why dynamic M query parameters might not work with SQL Server on Power BI Report Server? Any suggestions on additional steps to troubleshoot or resolve this issue would be greatly appreciated.
       
       

       Thanks in advance!

[Anonymous]

Hi @VarunDC ,

Ask what connection mode you are using, if you are using direct query mode, test it directly in Power BI Desktop. Make sure that the dynamic M query parameters work as expected there.
If so, the issue may be specific to the Power BI Reporting Server environment.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VarunDC]

Hi @Anonymous ,

Yes I am encountering an issue with the Direct Query mode. While it works as expected in Power BI Desktop, the filter value is not being passed to the parameter when I publish the report to Power BI Report Server. Could you please assist in resolving this discrepancy?

Thanks in advance

[Anonymous · Accepted solution]

Hi @VarunDC ,

Dynamic M query parameters can not work with SQL Server on Power BI Report Server.

Below is the official link will help you:

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

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.