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

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!