Changing the Data Source dynamically in Power Bi using Parameters
Parameters in Power BI , basically is used for changing the argument values for particular transforms and data source functions and inputs in custom functions.
Sometimes it happens like you are working around a Power BI report connected to Development Database, and just in case you want to validate the report data, or dax calcultion with real data in Production Database for this you will have to change data source connection string, and that can be fine if you have to do this one or two time, but if you deal with this situtation frequently then it is good idea to create a parameter for this for changing data source dynamically.
Here we have two Databases as Dev_Db and Prod_Db shown below.
Lets prepare a data for report, first quickly create a tables in these database and insert some data into table.
For Prod_Db:
USE Prod_Db;
CREATE TABLE dbo.demo
(Id INT IDENTITY(1,1),
Name VARCHAR(50),
Country VARCHAR(50),
CreatedBy VARCHAR(15)
)
INSERT INTO dbo.demo (Name, Country, CreatedBy)
VALUES('Joshep', 'US', 'Prod Admin'),
('Sanjay', 'India', 'Prod Admin'),
('Mark Hanery', 'South Africa', 'Prod Admin'),
('Suraj', 'India', 'Prod Admin'),
('Nadal', 'Canada', 'Prod Admin'),
('Jhong', 'Malaysia', 'Prod Admin'),
('Jubed', 'Pakistan', 'Prod Admin'),
('Sartaj Khan','Pakistan', 'Prod Admin'),
('Sampan Singh', 'India', 'Prod Admin'),
('Cheong', 'China', 'Prod Admin')
For Dev_Db:
USE Dev_Db;
CREATE TABLE dbo.demo
(Id INT IDENTITY(1,1),
Name VARCHAR(50),
Country VARCHAR(50),
CreatedBy VARCHAR(15)
)
INSERT INTO dbo.demo (Name, Country, CreatedBy)
VALUES('Joshep', 'US', 'Dev Admin'),
('Sanjay', 'India', 'Dev Admin'),
('Mark Hanery', 'South Africa', 'Dev Admin'),
('Suraj', 'India', 'Dev Admin'),
('Nadal', 'Canada', 'Dev Admin'),
('Jhong', 'Malaysia', 'Dev Admin'),
('Jubed', 'Pakistan', 'Dev Admin'),
('Sartaj Khan','Pakistan', 'Dev Admin'),
('Sampan Singh', 'India', 'Dev Admin'),
('Cheong', 'China', 'Dev Admin')
As you can see, for Prod_Db we have inserted a value Prod Admin in CreatedBy column of table demo while for Dev_Db database table demo the CreatedBy column values is inserted as Dev Admin.
It is done just to get to know that data that is being displayed by report is connected either from Dev or Prod Database’s table.
First Go to Power Query Editor, Next click in Manage Parameters tab in Ribbon Bar after that select New Parameter from context menu as shown below.
After clicking on New Parameter, a Manage Parameters dialog box opens.
Now you need to provide a Parameter name, Suggested values,default value and current value as shown below.
In suggested values, select the List of values and provide the name of Databases for suggestion, we have provide Prod_Db and Dev_Db names.
Then provide the Default value and current value for parameter that we have selected as Dev_Db.
Once you click on Ok Button, you will see a parameter named Database is created with Current value set to Dev_Db.Â
Now save the changes click on Close & Apply.Â
Connect to SQL Server Data Source in Power BI using Parameters
Lets connect to SQL Server Database using Parameters. Click on Get Data in Ribbon bar, then select SQL Server Database and Click on Connect.
Once you click on connect, a SQL Server Database dialog box opens, here we will use parameter that we created for providing a Database name dynamically.
Once you click on Ok button, You will see a Data from Dev_Db database table’s is populated on screen, as Parameter Current value is set to Dev_Db.
After clicking on Load button, you will see data is loaded to Model..
And after on successful loading, you will see a table is created named Query1 in data model.
Lets take this data into table visual as shown below, By Default data is fetched from Development database table as current value to Parameter is defined as Dev_Db.
Lets change the Data Source to connect with Prod_Db using Parameter.
Click on Transform Data tab in Ribbon bar, and select Edit Parameters.
Once you click on Edit parameters, a Edit Parameters dialog box opens, which suggests you the database name. Select Prod_Db to connect the report to Production database.
After that click on Ok Button.
After clicking OK button, you will see now the report’s data is connected to Prod_Db and you can see that now value in CreatedBy column is showing as Prod Admin which confirms that now data in report are fetched from Prod database’s table.
You can see, how parameter is applied on Query to select datasource dynamically.
Go to Power Query Editor and see the M statement for Data model, you will see that database name is taken through parameter named Database.
In similar way, you can create parameters as per your requirement.
Also Read..
