If you are a Power BI report developer, you might have experienced the case I’m describing in this post. You did your utmost best to setup a decent DT(A)P process, where you clearly separate development, test, acceptance and production stages from each other. But as you don’t want to be the only one that has access to all the environments, you granted access to all stages for your main stakeholder.

But here’s the challenge. Every time you inform them that new changes have been made or if they need to validate the data, they keep looking at the wrong stage! Despite you clearly added a prefix or suffix to the workspace name which identifies the stage. They go to your development report and might give you feedback that the data is incomplete, or unfinished features are in the report.

What else can you do to prevent this from happening? In this blog I will describe how you can add a visual identifier to your report which directly takes the attention of the user and tells them at which stage they look.

Stage identifier

As described in the intro of this post, you already put effort in a naming convention that includes the stage of your development cycle. For example a prefix or suffix to the workspace name where you mention DEV, TST, ACC or PRD. However, your key users keep looking at the wrong stage.

Next to the stage identifier in your workspace name, you will also see tags added to the workspaces in case you use Power BI deployment pipelines for release management. These identifiers show up in the top of the workspace overview like shown below.

Power BI does not offer a native feature that identifies the stage in a visual way in the report itself. But with a little trick you can add a visual identifier yourself to the report by making use of conditional formatting in Power BI. In my example, I added an orange bar on top that shows a warning when they look at development, yellow bar when they look at test and the bar disappears when they look at production.

Step by step guide

In this section I will run you through a step by step guide on how to get this working. For this example, I used the Dashboard in a Day report, which is one of the standard demo reports of Microsoft.

Step 1: Adding a table to the model

We want to automatically adjust the bar color and text in the bar for each stage, we adjust this based on a parameter (will be created in step 2), so we start with adding a table to the model. We can do this by simply using the Enter data option in the top ribbon. We populate a table with three columns and fill it for each stage.

• Stage name, just a simple name which we will steer based on the parameter.
• Stage color, we will use this later for the bar and this must be the HEX color code.
• Stage warning text, the text to show in the bar.

In case we want to hide the bar in total for production, then use the color coding identical to your background color for the production stage and leave the Stage Warning Text empty like below.

Step 2: Adding a the parameter

Next, we create a parameter, as we always want to have only one row of data in the table we just added. Based on the parameter value we filter on either Development, Test or Production.

Step 3: Filter the table by the parameter value

Now, we filter the table based on the parameter we just created, so we only have one row left in our table and we can click Close & Apply to load the data to the model.

Step 4: Build measures to swap values can conditional formatting

As we want to automatically adjust the bar color and text shown, we want Power BI to only pick up the value we have. In order to use it in the conditional formatting, we need to build two DAX measures to return the values we want. Based on the column names I used, the measures will look like below.

Stage Color Formatting = SELECTEDVALUE('Visual Stage Identifier'[Stage Color]) 

Stage Warning Text Formatting = SELECTEDVALUE('Visual Stage Identifier'[Stage Warning Text])

Step 5: Add a textbox / smart narrative to the canvas

In the report canvas, we add a text box to the canvas. Since we can use smart narratives, we can directly call the measure value we just created like this:

When you configure it like above, you will see the text value directly appear in the text box. This smart narrative will now capture the value of the measure we just created.

Step 6: Conditional formatting for the background color

We also added a color coding to our table that we want to use for the background color. Next is to go to the format pane of the text box / smart narrative, where we search for the Effects settings. Here we will find the background settings. By switching on the background, the fx symbol becomes available.

With the fx we can conditionally format the background color. As we already build a measure to return us the HEX color code, we can configure it like below to use the field value from our earlier created measure. Possibly you can also adjust the transparency of the bar in the same settings.

So far, so good. We have the bar shown in Power BI Desktop and based on the parameter, we can directly switch to test mode, or even production mode. In order to apply the change, we have to click close and apply again in Power BI desktop, as we basically changed the filter in our Power Query expression.

Step 7: Adjust parameter based on deployment rules

The final step we want to make, is to adjust this parameter automatically when we move content from Development to Test and finally to Production. Earlier in this blog I already referred to Power BI Deployment Pipelines where we can define deployment rules to automatically adjust this parameter value.

To start with, you need to setup a Power BI deployment. The Microsoft documentation to get started with deployment pipelines will guide you through these steps.

Once we have the deployment pipeline setup, we can configure the deployment rule by clicking on the lightning icon on the right top of a stage. Here we can define the rules for a parameter, so we can automatically adjust the value once we deploy to the Test stage. Similar you can configure the update the parameter value for Production.

Wrap-up

I hope in this blog I have shown how you can add another identification layer to your report to identify the stage in which deployment stage it is in. This should definitely help and based on the colors you choose take the attention of your end users.

One important thing to keep in mind that the table with messages and colors is loaded as part of your data model. In order to reflect the parameter update, you need to refresh the dataset (or at least this table) before it is applicable. If you do not want to refresh your entire dataset, you can consider to refresh only this specific table by using the enhanced refresh API.

Last, this setup has shown how to update the parameter value based on deployment rules in a deployment pipeline. However, this requires to have any type of premium assigned to each stage workspace, as deployment pipelines is a premium feature. In case you do not have the availability of premium capabilities, you can always consider to manually update the parameter value in the dataset settings in the Power BI Service as described here or even automating this step using the REST API to update parameters.

For completeness, I have uploaded my sample file to my GitHub repository for Power BI Examples.