Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
VarunDC
Regular Visitor

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @VarunDC ,

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

vxiandatmsft_0-1721354664565.png

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.

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

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
Not applicable

Hi @VarunDC ,

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

vxiandatmsft_0-1721354664565.png

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.

 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.