Forum Discussion

Lio123's avatar
Lio123
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Help on Formatting the specific part of the string dynamically

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,

Dax_Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

     

9 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Lio123
      Icon for Advocate I rankAdvocate I
      1 year ago

      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?

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Lio123
      Icon for Advocate I rankAdvocate I
      1 year ago

      Hi Poojara_D12 ,

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

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Lio123
      Icon for Advocate I rankAdvocate I
      1 year ago

      Hi Anonymous ,
      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.