Power BI service parameter to use in query

[Newcolator]

Hello

We have Power BI reports sourcing data from Snowflake. We have two Snowflake environments, PROD and TEST, and we also have a PROD and TEST workspace in the Power BI service. Is it possible to automate the connection to Snowflake so that the reports published to the PROD workspace use the Snowflake PROD database, and reports published to the TEST workspace use the Snowflake TEST database?

I want to avoid having to change the reports manually. Is there a way to use a parameter based on the workspace the report is published to?

Thanks!

[v-lgarikapat]

Hi @Newcolator 

Thanks for reaching out to the Microsoft fabric community forum.

@Akash_Varuna ,

Thanks for your prompt response

In addition to  @Akash_Varuna 

To implement environment specific Snowflake connections in Power BI without manual changes, start by creating a text parameter in Power BI Desktop called Environment with values like PROD and TEST. Use this parameter in your Power Query M code to dynamically set the database name or other Snowflake connection details (e.g., DatabaseName = if Environment = "PROD" then "SNOWFLAKE_PROD_DB" else "SNOWFLAKE_TEST_DB"). Reference this dynamic database name in your Snowflake connector. Publish the report to the Power BI Service, and in the dataset settings for each workspace (PROD or TEST), configure the Environment parameter to the appropriate value. To automate this switching process, use Power BI Deployment Pipelines: set up a pipeline with Development, Test, and Production stages, and define parameter rules for each stage so the correct environment is applied automatically during promotion. This avoids manual editing and ensures the correct Snowflake database is used based on the stage or workspace.

Kindly refer to the learning document and threads below they may help you.

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

Solved: Power BI Deployment Pipeline: How to Automatically... - Microsoft Fabric Community

Solved: Use power bi parameters in a query through native ... - Microsoft Fabric Community

If this post helped resolve your issue, please consider giving it Kudos and marking it as the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[Akash_Varuna · Accepted solution]

Hi @Newcolator Yes, you can automate this by creating a parameter in Power BI Desktop for the Snowflake environment and using it in your query. After publishing, set the parameter value in the dataset settings of each workspace (PROD or TEST). Use deployment pipelines to switch parameter values automatically when moving reports between workspaces.