During a project, I had the following similar requirement: “to show the number of products with sales in 2019 below 10,000€ in a text box. And if this number of projects is greater than 10, the font color should be red otherwise green”.

I know this might sound straightforward. But I came across some problems that I would like to share with you and of course the solution I found. In case you know another solution, please share with us in the comments.

In this post we are going to talk about:

• How to calculate the number of products with sales in 2019 over and under the threshold of 10,000€

• How to change the font color of the products number dynamically in a text box

A simplified version of the Contoso database is used in this example.

Step 1: calculate the number of products with sales in 2019 over and under the threshold of 10,000€

We need to create 2 DAX measures to calculate the number of products over and under the threshold of 10,000€. These two measures will be used later in a text box.

Strategy to solve the problem

Before we dive into the DAX code, let’s think about what we want to calculate. The idea is to create a virtual table with two columns: Product Name and Indicator. The column Indicator returns “1” if sales are smaller than 10,000€ and “0” (zero) if equal or greater than 10,000€. See below the virtual table as an example (don’t worry, we are going to get into the DAX behind it):

Creating the DAX measures
An additional measure is created named “Measure helper threshold”, in order to return “1” if Sales are smaller than 10,000€ or “0” (zero) if greater or equal to 10,000€.

After that we create the following two DAX measures (see below the DAX code explanation):

Now that we created the 3 DAX measures needed, we can go to step 2 where we create the text box and add the conditional formatting.

Step 2: how to change the font color of the products number dynamically in a text box

The first thing to do is to insert a text box in your report. In this example, I added a static text and then the two DAX measures created above (“Sales 2019 Threshold Under” and “Sales 2019 Threshold Over”).

See below how it looks like:

The trick here is to insert both DAX measures in the text box. When publishing the report into Power BI Service, this blank will disappear. The next step is to add the desired font color selecting the text and changing the color manually inside the text box. In this example, we have the following condition for the font color:

• If number of products with sales in 2019 below 10,000€ is greater than 10, the font color should be red

• If number of products with sales in 2019 below 10,000€ is smaller or equal to 10, the font color should be green

See below how it looks like after publishing the report:

As you can see, the color changes automatically according to the condition we defined earlier. Also, note that the blank value is gone!

Conclusion

In this article, we demonstrated a way to add dynamic conditional formatting in a text box in Power BI. With a few workarounds it was possible to implement this.

In case you know another solution, please share with us in the comments.

Thanks for reading! Let me know your thoughts in the comments below.

Last updated on October 06, 2023