Within the earlier posts, right here and right here, I defined how you need to use Energy BI Desktop Question Parameters for a lot of totally different use instances. Energy BI improvement crew added one other cool function to Energy BI Desktop on July 2016 which is the flexibility so as to add a Listing Question output to a question parameter because it’s “Prompt Values” (previously “Allowed Values”). This function could be very helpful and any further we’re not restricted to proviode a static checklist of values in “Handle Parameters”. On this submit I present you use an inventory output in question parameters.
Observe: This function is NOT out there in DirectQuery mode on the time of penning this submit.
On this submit as standard I’ll connect with a SQL Server database as a pattern. To have the ability to observe this submit it’s important to have:
- The most recent model of Energy BI Desktop (present model is 2.38.4491.282 64-bit (August 2016))
- AdventureWorksDW
Within the first submit of those sequence I defined create dynamic information sources utilizing Question Parameters. You additionally learnt use Question Parameters in Filter Rows. However, what if we wish to filter question outcomes based mostly on the values of a column from a selected desk? Beforehand we couldn’t reply these kind of questions if we wish to filter FactInternetSales based mostly on a specific values of EnglishProductName column from DimProductCategories utilizing Question Parameters. However, now we will simply implement these kind of situations.
Let’s implement this state of affairs.
Loading Knowledge into the Mannequin:
- Open Energy BI Desktop
- Get information from SQL Server and connect with Journey Works DW 2016 CTP3
- Choose “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click on “Load”
- Change to “Relationships” view to ensure the relationships detected accurately then click on “Edit Queries” from the ribbon
Making a Listing Question from a Desk Column:
Now we have to create an inventory from “DimProductCategory” desk. To take action:
- In Question Editor window click on “DimProductCategory” from Queries pane
- Proper click on on “EnglishProductCategoryName” and choose “Add as New Question”
Observe: You possibly can create an inventory from a column by choosing “Drill Down”. The distinction between choosing “Drill Down” or “Add as New Question” is that “Drill Down will flip the present question to an inventory whereas “Add as New Question” will create a brand new checklist question.
Observe: You may as well use Desk.ToList() perform in Energy Question (M) language to create an inventory from a desk.
Making a New Question Parameter and Hyperlink it to the Listing Question:
Now it’s time to create a question parameter and hyperlink to the Listing Question.
-
In Question Editor click on “Handle Parameters” from the ribbon
-
Click on “New”
-
Change the identify to “Product Class”
-
In our pattern this parameter isn’t required so un-tick “Required”
-
Change “Kind” to Textual content
-
In “Prompt Values” (it was once Allowed Values) choose “Question”
-
Choose “EnglishProductCategoryName” for “Question”
-
Kind “Equipment” in “Present Worth” then click on OK
Reference the Parameter by way of Filter Rows
Now we have to add a filter to DimProductCategory and reference the parameter. Within the first a part of these sequence I defined reference a parameter by way of filter rows so I simply shortly undergo the steps.
Thus far we created a parameter on high of an inventory which we created from a desk column. We additionally added a row filter to “DimProductCategory”. Now we wish to use that parameter in motion.
Use the Parameter in Motion:
As per the state of affairs the purpose is to filter “FactInternetSales” information utilizing the parameter. However we added a filter to “DimProductCategory”. The “DimProductCategory” is certainly a grasp desk for “DimproductSubCategory” and the afterward is a grasp desk for “DimProduct”. So resulting from referential integrity after we filter the “DimProductCategory” desk it ought to mechanically filter all different element tabled right down to the “FactInternetSales”. Let’s see the way it actually works in Energy BI Desktop.
- Change to report view
- Put a Matrix on the report web page
- Develop “FactInternetSales” then tick “SalesAmount”
- Develop “DimProductCategory” then choose “EnglishProductCategory”
As you may see there’s a clean merchandise within the Matrix. The reason being as a result of not all rows within the FactInternetSales have a matched row of their grasp desk after we added the row filter which leads them to be proven as clean. That is extra smart if we add “EnglishProductSubCategory” from “DimProductSubCategory” to the Matrix rows.
To beat this we simply have to filter out the blanks from the outcomes.
- Within the “Fields” pane scroll down and discover “EnglishProductCategoryName” in “Filters”
- Develop “EnglishProductCategoryName” filter
- Change “Filter Kind” to “Superior filtering”
- Choose “in not clean” from “Present objects when the worth:” dropdown checklist
- Click on “Apply filter”
The issue is solved.
Now we will change the “Product Class” parameter to one thing else, say “Bikes” and see the outcomes. To take action:
- Click on “Edit Queries” from the ribbon then choose “Edit Parameters”
- Change the worth to “Bikes” then click on OK
- Click on “Apply Adjustments”
All executed!
