Forum Discussion

Poornima2023's avatar
Poornima2023
Icon for Helper I rankHelper I
11 months ago
Solved

Help with conditional formatting in power bi

Hi I need help with formatting of matrix table like below.  
  • sivarajan21's avatar
    sivarajan21
    11 months ago

    Hi Poornima2023 ,

     

    Finally I achieved this using SVG:

     

    This svg feature helps to give conditional formatting based on the data we have.
    Here is the mcode that i used:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck0sLlHSUXLKKM0rS8xLLc4oTyxSitWJVvLLLyrJAMo4ZyTmpWSmJxZlgIWD80uhwql5eYmZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, #"Branch " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Branch ", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "svg", each if [Region] = "East" then 
"data:image/svg+xml;utf8," &
"<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" &
"<rect x='10' y='10' width='180' height='80' fill='#2196F3' stroke='#333' stroke-width='2' rx='10' ry='10'/>" &
"<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>East</text>" &
"</svg>"

 else if [Region] = "North" then "data&colon;image/svg+xml;utf8," &
"<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" &
"<rect x='10' y='10' width='180' height='80' fill='#4CAF50' stroke='#333' stroke-width='2' rx='10' ry='10'/>" &
"<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>North</text>" &
"</svg>"
 else if [Region] = "South" then 
"data&colon;image/svg+xml;utf8," &
"<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" &
"<rect x='10' y='10' width='180' height='80' fill='#FF9800' stroke='#333' stroke-width='2' rx='10' ry='10'/>" &
"<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>South</text>" &
"</svg>"
 else null)
in
    #"Added Conditional Column"



    Does this resolve your issue? let me know

     

    Best regards,

Poornima2023's avatar
Poornima2023
Icon for Helper I rankHelper I
11 months ago

Please help. Also note its not table its matrix.

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
11 months ago

Hi Poornima2023 

 

I have a workaround for this if you don't have python installed.

We can create a SVG specification:

1. Create an SVG specification that includes a rectangle shape and a text element within it.
2. Create a conditional column in Power Query and place your specification in that column. Replace the text value in the SVG code with the column you want to add to your matrix row header. Also, replace the rectangle shape’s fill color with the color you want to use for that category. (You may need to write conditional logic using an IF statement to make it dynamic.)
3. Change the category of this column to "Image URL."
4. You can then place it in your matrix row header.

 

If you want i can implement this and give you the code and result

 

Please let me know!

Best Regards,