Hello! I need to figure out a way to easily trigger multiple source changes by using one parameter. Here's the situation:
I have multiple sql datasources coming from a few different databases. Each database has a development and a production version. There is also a development and production Power BI environment. When moving reports from the development environment to the production environment, I would like to be able to make one parameter that can change all the data sources at once AND allows us to refresh the data sources on the report server. I know how to create a parameter to change one of the data bases, but not how to link multiple of these sources together. Basically, I think I'm looking for something like cascading parameters.
Here's what I've tried so far:
Attempt 1: Creating an IF statment as the source for my query. This looked like the below:
Sql.Database(
if #"Parameter"="Development" then "Development_DB" else "Production_DB"
, "Database_Name", ...Query stuff....)
This would not allow refreshes on the server.
Attempt 2: Creating an IF statement for the whole source. This looked like the below:
if #"Parameter"="Development" then Sql.Database(
"Development_DB"
, "Database_Name", ...Query stuff....)
else Sql.Database(
"Production_DB"
, "Database_Name", ...Query stuff....)
This required me to enter credentials for both data sources on each site. Not acceptable to our team.
Attempt 3: Creating a blank query and using an IF statment to give it just the value of the required database. The thought here was that I could create a new parameter and assign this table as the value to user in my data sources. (This was my attempt at cascading parameters.) The parameter would not let me select the table as a Query. Table below:
#table(
type table
[
#"Text Column"=text
],
{
{if #"Parameter"="Development" then "Development_DB" else "Production_DB"}
}
)
Now I'm out of ideas and I haven't been able to google anything to help me out. Has anyone done anything like this before? Any ideas?
