How It Works

To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?

Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.

Open Query Editor

Click “Manage Parameters”

Click “New”

Enter “Name” and “description”
Tick “Required”
Select “Text” in “Type”
Select “List of values” in “Suggested Values” and type in server names for different environments
pick a “Default Value” and “Current Value”
Click OK

Creating new parameter in Power BI Query Editor

So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.

Continue reading →

List Output in Power BI Query Parameters

In the previous posts, here and here, I explained how you can use Power BI Desktop Query Parameters for many different use cases. Power BI development team added another cool feature to Power BI Desktop on July 2016 which is the ability to add a List Query output to a query parameter as it’s “Suggested Values” (formerly “Allowed Values”). This feature is very useful and from now on we are not restricted to proviode a static list of values in “Manage Parameters”. In this post I show you how to use a list output in query parameters.

Note: This feature is NOT available in DirectQuery mode at the time of writing this post.

Requirements

In this post as usual I’ll connect to a SQL Server database as a sample. To be able to follow this post you have to have:

The latest version of Power BI Desktop (current version is 2.38.4491.282 64-bit (August 2016))
AdventureWorksDW

Scenario

In the first post of these series I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. But, what if we want to filter query results based on the values of a column from a particular table? Previously we couldn’t answer these sort of questions if we want to filter FactInternetSales based on a selected values of EnglishProductName column from DimProductCategories using Query Parameters. But, now we can easily implement those sort of scenarios.

Let’s implement this scenario.

Loading Data into the Model:

Open Power BI Desktop
Get data from SQL Server and connect to Adventure Works DW 2016 CTP3
Select “FactInternetSales”, “DimProduct”, “DimProductSubCategory” and “DimProductCategory” tables then click “Load”