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,

[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.

[DataNinja777]

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?