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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MojoGene
Post Patron
Post Patron

Use PBI Template with parameter for different database??

Not sure if this is possbile with parameters and PBI templates.

 

Assume that we have Company A and Company B both of which are using a sales database with identical structure (same tables, fields, etc.), but the data in each of the databases are unique to each Company. Company A's datebase is named = Sql.Databases("CompanyA") and Company B's database is named = Sql.Databases("CompanyB").

 

Can a template be used to connect to either/both of the databases and if so how would I created the list of Accepted Values that would prompt a user which database to connect to?

 

Thanks.

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @MojoGene 

 

I'd like to suggest you create three parameters as below.

d1.png

 

d2.png

 

 

d3.png

 

Then you may click 'Advanced Editor' and parameterize the data source. I use SQL Server to test.

d4.png

 

Then you need to save it as a template file(pbit file).

 

When you open the pbit file, you may input the corresponding parameter to connect to the specific table.

d5.png

 

For further information, I'd like to suggest you refer the following links.

Create report templates for Power BI Desktop 

Deep Dive into Query Parameters and Power BI Templates 

Power BI Desktop Query Parameters 

 

Best Regards

Allan

 

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

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @MojoGene 

 

I'd like to suggest you create three parameters as below.

d1.png

 

d2.png

 

 

d3.png

 

Then you may click 'Advanced Editor' and parameterize the data source. I use SQL Server to test.

d4.png

 

Then you need to save it as a template file(pbit file).

 

When you open the pbit file, you may input the corresponding parameter to connect to the specific table.

d5.png

 

For further information, I'd like to suggest you refer the following links.

Create report templates for Power BI Desktop 

Deep Dive into Query Parameters and Power BI Templates 

Power BI Desktop Query Parameters 

 

Best Regards

Allan

 

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

mahoneypat
Microsoft Employee
Microsoft Employee

Best way to do this would be to make a parameter in the query editor to hold your database name(s).  You would then use this parameter name in your query.  If you want single select scenario and the user picks (parameters can only hold a single value), you would have a query that queries the database for that one value.  If you want the multiple option (all listed databased are queried, and user can pick desired database(s) from a slicer), you would make a table with the list of database names and then make a custom function that accepts same parameter but the database name for that row as input.  The results would all be appended, and you would still have the database name column for slicing.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Pat:

 

Thanks for jumping in. 

 

I could create a list with the database names for each Company. That seems simple enough. But how do I pass the selected list value as the Source for each table in the database?

 

Create a custom function in query editor and in the query with the table with the list of database names, on the Add tab choose Invoke Custom Function, choosing the column with your database names as the input paramter.  See this post for instructions.

https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-f...

 

To create the function, make a query that does what you want with one of the database, and then modify the query in advanced editor, replacing the database name with the name of your parameter.  In example below, the parameter name is in parentheses.  The values from your table of names is inserted there when you invoke custom function.

 

(databasename) =>

 

Source = sql.database(actualservername, databasename, ...)

 

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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