Within the earlier posts, right here and right here, I defined how you should use Energy BI Desktop Question Parameters for a lot of totally different use instances. Energy BI improvement staff added one other cool function to Energy BI Desktop on July 2016 which is the power so as to add a Checklist Question output to a question parameter because it’s “Recommended Values” (previously “Allowed Values”). This function could be very helpful and to any extent further we aren’t restricted to proviode a static checklist of values in “Handle Parameters”. On this put up I present you how you can use a listing output in question parameters.
Word: This function is NOT obtainable in DirectQuery mode on the time of penning this put up.
On this put up as normal I’ll hook up with a SQL Server database as a pattern. To have the ability to observe this put up 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 put up of those collection I defined how you can create dynamic knowledge sources utilizing Question Parameters. You additionally learnt how you can use Question Parameters in Filter Rows. However, what if we need to filter question outcomes primarily based on the values of a column from a selected desk? Beforehand we couldn’t reply these form of questions if we need to filter FactInternetSales primarily based on a specific values of EnglishProductName column from DimProductCategories utilizing Question Parameters. However, now we will simply implement these form of eventualities.
Let’s implement this state of affairs.
Loading Knowledge into the Mannequin:
- Open Energy BI Desktop
- Get knowledge from SQL Server and hook up 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 Checklist Question from a Desk Column:
Now we have to create a listing 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”
Word: You may create a listing from a column by deciding on “Drill Down”. The distinction between deciding on “Drill Down” or “Add as New Question” is that “Drill Down will flip the present question to a listing whereas “Add as New Question” will create a brand new checklist question.
Word: You may as well use Desk.ToList() operate in Energy Question (M) language to create a listing from a desk.
Making a New Question Parameter and Hyperlink it to the Checklist Question:
Now it’s time to create a question parameter and hyperlink to the Checklist 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 just isn’t required so un-tick “Required”
Change “Kind” to Textual content
In “Recommended Values” (it was 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 collection I defined how you can reference a parameter by way of filter rows so I simply rapidly undergo the steps.
To this point we created a parameter on prime of a listing which we created from a desk column. We additionally added a row filter to “DimProductCategory”. Now we need to use that parameter in motion.
Use the Parameter in Motion:
As per the state of affairs the aim is to filter “FactInternetSales” knowledge 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 as a consequence of referential integrity after we filter the “DimProductCategory” desk it ought to robotically 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 possibly can 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 must 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 gadgets 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 Modifications”
All achieved!
