Forum Discussion

Poornima2023's avatar
Poornima2023
Helper 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,

sivarajan21's avatar
sivarajan21
Post Prodigy
11 months ago

Hi Poornima2023 

 

Do you want expected output as below? I created a solution as below:

we can color the region and all columns as shown above image.

Please let me know and i will inform the solution for you

 

 

Poornima2023's avatar
Poornima2023
Helper I
11 months ago

Please help. Also note its not table its matrix.

  • sivarajan21's avatar
    sivarajan21
    Post Prodigy
    11 months ago

    Hi Poornima2023 

     

    Sure! 

    I used plotly chart using python script to achieve this

    You need to just click the PY in visualization and type in below code on scripts to achieve this. 

    Before that do you have python installed on your machine?

    Currently power bi doesn't provide formatting on all columns

     

    Please let me know

     

    Best Regards,

  • sivarajan21's avatar
    sivarajan21
    Post 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,