When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.
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?
Read more about query parameters from a list output here.
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
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.
- Right click on the “Servers” parameter and select “Reference”
- This creates a new query referencing the parameter’s “Current Value”
- Rename the query to Environments
- Click “To Table” button from “Convert” section from the ribbon
- Rename the “Column1” column to “Server”
- from “Add Column” tab from the ribbon click “Custom Column”
- Type in a name for the new column
- in the formula type something the following (the formula might be different in your case)
if [Server] = ".\SQL2017" then "" else if [Server] = ".\SQL2019" then "UAT" else "Dev"- Click OK
I don’t want to show anything when I switch to production server so I put “null” for my production server.
We’re almost done. The only remaining piece of the puzzle would be applying the changes and put the environment in a Card visual in our report.
- Click “Close & Apply” from “Home” tab from the ribbon
- Now put a Card visual on the report canvas
- Select “Environment” column
- Disable “Category Label” from the Card
All done!
Now let’s test it.
- From “Edit Queries” button from “Home” tab click “Edit Parameters”
- Switch parameter value by selecting a different value from the list
- Click OK
- Click “Apply Changes”
There we go. It is working perfectly.
Here is a screenshot of a report built with the same technique that clearly shows the environment the report is connected to. When I select a production server then the card shows nothing so it is would be invisible in production environment while in other environments it is quite clear which environment my report is connected to.
Have you ever used this technique? Have you faced any challenges? Have you done it differently? Please share with us your thoughts in the comment section below.
