One of many coolest options added to the April 2016 launch of Energy BI Desktop is “Question Parameters”. With Question Parameters we will now create parameters in Energy BI Desktop and use them in numerous instances. For example, we will now outline a question referencing a parameter to retrieve completely different datasets. Or we will reference parameters by way of Filter Rows. Usually talking we will reference parameters by way of:
Take away Rows
As well as, parameters might be loaded to the Information Mannequin in order that we will reference them from measures, calculated columns, calculated tables and report parts.
In “Energy BI Desktop Question Parameters” sequence of articles I present you the best way to use Question Parameters in numerous situations.
On this article I’ll present you some use instances of Question Parameters based mostly on some situations as beneath:
Parameterising a Information Supply
Utilizing Question Parameters in Filter Rows
You’ll be taught extra about Question Parameters within the subsequent articles “Energy BI Desktop Question Parameters, Half 2, SQL Server Dynamic Information Masking Use Case” and “Energy BI Question Parameters, Half 3, Checklist Output“
You’ll require to fulfill the next necessities to have the ability to observe this submit:
The most recent model of Energy BI Desktop (Model: 2.34.4372.322 64-bit (April 2016) or later)
Notice: As Dynamic Information Masking (DDM) is a brand new function of SQL Server 2016 and it’s not accessible within the earlier variations of SQL Server that you must set up the most recent model of SQL Server 2016. So you will want SQL Server 2016 and Journey Works CTP3 solely if you wish to use Question Parameters on high of Dynamic Information Masking (DDM).
Parameterising a Information Supply could possibly be utilized in many various use instances. From connecting to completely different information sources outlined in Question Parameters to load completely different mixtures of columns. To make it extra clear I break down the situation to some extra particular use instances.
Use Case 1: Parameterising Information Supply to Connect with Totally different Servers and Totally different Databases
Suppose you’ve gotten completely different prospects utilizing the identical database schema. However, the databases hosted in numerous cases of SQL Server and in addition the database names are completely different. With Question Parameters we will simply swap between completely different information sources then publish the stories to every prospects’ Energy BI Service.
1-Click on “New”
2-Kind a reputation for the parameter
3-You can too write an outline
4-Choose Kind as Textual content
5-From “Allowed Values” choose “Checklist of Values”. This opens a listing which you can sort in numerous values for the parameter. If you happen to don’t need to enter ant predefined values for the parameter choose “Any worth” for “Allowed Values”
6-Fill the checklist with some legitimate values. In our case it could be occasion names
7, 8, 9-Choose a “Default Worth” and “Present Worth” then click on OK
Use Case 2: Loading Dynamic Columns from the Information Supply
Keep in mind the earlier use case. We had completely different prospects having completely different databases on completely different servers. Our prospects even have completely different reporting wants. For example, they should see their prospects’ names in numerous shapes. The shoppers information saved in DimCustomer. We should always cowl the mixtures beneath for “Buyer Title” column:
1- Buyer Title = LastName from DimCustomer
2- Buyer Title = FirstName + LastName from DimCustomer
3- Buyer Title = LastName + FirstName from DimCustomer
4- Buyer Title = FirstName + MiddleName + LastName from DimCustomer
To help this we will create a parameter containing all mixtures above for Buyer Title.
Delete DimCustomer from the mannequin we created for the earlier use case
Create a brand new parameter and add all wanted mixtures within the checklist of values. You are able to do this by proper clicking on the Queries pane then click on “New Parameter” or by clicking on “Handle Parameters” from the ribbon
Now we have to import DimCustomer into the mannequin once more. (Keep in mind, we eliminated DimCustomer in pervious steps)
Click on “New Supply” from the ribbon on Question Editor window
Click on “SQL Server Database” then “Join”
Choose “Server” and “Database” parameters
Click on “Superior choices”
Put the next SQL assertion