Help on Formatting the specific part of the string dynamically

[Lio123]

Dear All, 

I need your help and kind support in the resolving the issue. 

Query : Please provide the DAX for this use case - Formatting the specific part of the string dynamically. 

Let's say, if paragraph contains Numbers, "YTD", "Achieved" or "Target". I want to highlight all of them in different color.
Please noted, paragraph changes every day.

Ex: This year we have achieved 100M and Target was 50M, our XYZ customers was the key customer and YTD is 300M


Thanks,

[v-mdharahman]

Hi @Lio123,

Thanks for reaching out to the Microsoft fabric community forum.

As suggested by @DataNinja777 and @Poojara_D12, to achieve dynamic formatting of specific words like "Achieved", "Target", "YTD", and numbers within a changing paragraph, you’ll need to use a custom visual because standard Power BI visuals don’t support inline HTML rendering. 

I would also take a moment to thank @Poojara_D12 and @DataNinja777, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Lio123]

Hi @v-mdharahman ,
Thanks for your response, could you guide me how to import a custom visual into Power BI, currently I do not have pro license. Also, please provide me the name of the custom visual which supports this HTML formatting. 

Your help will be greatly appriciated. 

[v-mdharahman]

Hi @Lio123,

You can use the “HTML Content” visual by CloudScope as this supports HTML tags like <span> and will render your color formatting exactly as needed. To import the Visual into Power BI Desktop

You don’t need a Pro license to use custom visuals in Power BI Desktop but need only for publishing/sharing to Service. 

To add the custom visual open Power BI Desktop and in the Visualizations pane, click on the three dots (...) at the bottom and select “Get more visuals”. In the pop-up window, search for "HTML Content" then click Add to import it into your report.

Once imported, you'll see it in your visuals pane. Drag it onto your report and bind it to the DAX measure that contains HTML..

Best Regards,

Hammad.

[v-mdharahman]

Hi @Lio123,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @Lio123,
Hope everything’s going smoothly on your end. As we haven’t heard back from you, so I wanted to check if the issue got sorted.
Still stuck? No worries just drop us a message and we can jump back in on the issue.

Best Regards,

Hammad.

[Poojara_D12]

Hi @Lio123 

• Native DAX cannot do rich text formatting alone.

• You can create DAX measures with HTML and render them in HTML-capable visuals.

• For more dynamic scenarios, consider preprocessing in Power Query or using scripting visuals.

[Lio123]

Hi @Poojara_D12 ,

Thank you for your response, would you mind providing the sample pbix file?

Thanks

[DataNinja777 · Accepted solution]

Hi @Lio123 ,

You can dynamically format parts of a string in Power BI by combining a DAX measure with a custom visual capable of rendering HTML. Standard visuals don't support this, so the solution involves creating an HTML-formatted string in DAX. To begin, you'll need to add the free HTML Content visual from the AppSource marketplace to your Power BI report.

Once the visual is added, you can create a DAX measure. This measure will take your original text and use the SUBSTITUTE function to find and replace your target keywords. Each keyword is replaced by itself wrapped in an HTML <span> tag, which allows you to apply inline CSS for styling, such as setting the color and font weight. It is important to remember that the SUBSTITUTE function is case-sensitive, so you may need to include separate steps for different capitalizations of a word, like "achieved" and "Achieved".

Here is an example DAX measure that formats the text based on your request. It assumes your paragraph is in a column named [Paragraph] within a table called MyData.

Formatted Paragraph = 
VAR OriginalText = SELECTEDVALUE(MyData[Paragraph])
VAR AchievedColor = "#28a745" -- Green
VAR TargetColor = "#fd7e14"   -- Orange
VAR YTDColor = "#007bff"      -- Blue
VAR NumberColor = "#6f42c1"  -- Purple

VAR Step1 = SUBSTITUTE(OriginalText, "achieved", "<span style='color:" & AchievedColor & "; font-weight:bold;'>achieved</span>")
VAR Step2 = SUBSTITUTE(Step1, "Achieved", "<span style='color:" & AchievedColor & "; font-weight:bold;'>Achieved</span>")
VAR Step3 = SUBSTITUTE(Step2, "Target", "<span style='color:" & TargetColor & "; font-weight:bold;'>Target</span>")
VAR Step4 = SUBSTITUTE(Step3, "YTD", "<span style='color:" & YTDColor & "; font-weight:bold;'>YTD</span>")
VAR Step5 = SUBSTITUTE(Step4, "100M", "<span style='color:" & NumberColor & "; font-weight:bold;'>100M</span>")
VAR Step6 = SUBSTITUTE(Step5, "50M", "<span style='color:" & NumberColor & "; font-weight:bold;'>50M</span>")
VAR FinalText = SUBSTITUTE(Step6, "300M", "<span style='color:" & NumberColor & "; font-weight:bold;'>300M</span>")

RETURN
FinalText

After creating this measure, drag it into the Values field of the HTML Content visual on your report canvas. The visual will then render the text with your specified formatting.

However, a critical limitation of this DAX-based approach is handling numbers that change daily. The SUBSTITUTE function requires you to know the exact text you're replacing. Since DAX lacks native pattern-matching capabilities (like regular expressions), it cannot easily find and format "any number" in a string. For a more robust and scalable solution for dynamic numbers, it's better to perform this transformation in the Power Query Editor. There, you can use functions to split the paragraph into individual words, check if each word is a number, apply the HTML formatting conditionally, and then rejoin the words into a sentence.

Best regards,

[Lio123]

Dear @DataNinja777  thank you so much for your fantistic explanation.

Could you also provide a custom visual name, and sample pbix file if possible please?