Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
08-27-2018 12:31 PM - last edited 08-28-2018 11:55 AM
One of the community blog posts that I have referrred to multiple times since it was published was @Dianapo's Power BI Desktop: Custom Aggregations, Formatting and Performance Indicators:
However, since discovering this technique I have run into some limitations with using UNICODE characters for indicators, namely the available shape and color options are fairly limited. However, the ability to tag measures as Image URL's and support for SVG functionality have provided a solution to these limitations that I present here. And since SVG Quick Measures are all the rage these days, I figured I'd create one of my own.
The two measures presented here provide a measure that produces different colors of all the same shapes based on some indicator criteria and another that modifies both color and shape based upon an indicator criteria. Enjoy!
Color changes with the same shape (based on a slicer selection):
Measure =
VAR __shape =
IF(HASONEVALUE(Shapes[Shape]),MAX(Shapes[Shape]),"Circle")
VAR __indicator = SUM([Column1])
VAR __radius = 9
VAR __opacity = 0.75
VAR __color =
SWITCH(
TRUE(),
__indicator<=-5,"DarkOrchid",
__indicator<=-4,"DarkBlue",
__indicator<=-3,"Blue",
__indicator<=-2,"FireBrick",
__indicator<=-1,"Red",
__indicator=0,"Gold",
__indicator>=5,"HotPink",
__indicator>=4,"Orange",
__indicator>=3,"Tomato",
__indicator>=2,"SpringGreen",
__indicator>=1,"Green",
"White"
)
VAR __header = "data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' width='50' height='20'>"
VAR __footer = "</svg>"
VAR __shapeTextCircle = "<circle cx='10' cy='10' r='" & __radius & "' fill='" & __color & "' fill-opacity='" & __opacity & "' />"
VAR __shapeTextTriangle = "<polygon points=""0,20 20,20 10,0"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextDot = "<circle cx='10' cy='10' r='" & 1 & "' fill='" & __color & "' fill-opacity='" & __opacity & "' />"
VAR __shapeTextSquare = "<polygon points=""0,20 0,0, 20,1 20,20"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextDiamond = "<polygon points=""10,0 20,10, 10,20 0,10"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextRect = "<polygon points=""0,20 0,0, 40,1 40,20"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextHex = "<polygon points=""10,0 20,4 20,16 10,20 0,16 0,4"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextOct = "<polygon points=""6,0 14,0 20,6 20,14 14,20 6,20 0,14 0,6"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextPent = "<polygon points=""10,0 20,8 16,20 4,20 0,8"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextStar = "<polygon points=""10,0 12,9 20,8 13,13 16,20 10,15 4,20 7,13 0,9 8,9"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeText =
SWITCH(__shape,
"Triangle",__shapeTextTriangle,
"Circle",__shapeTextCircle,
"Dot",__shapeTextDot,
"Square",__shapeTextSquare,
"Rectangle",__shapeTextRect,
"Hexagon",__shapeTextHex,
"Octagon",__shapeTextOct,
"Pentagon",__shapeTextPent,
"Star",__shapeTextStar,
"Diamond",__shapeTextDiamond,
__shapeTextCircle
)
VAR __return = __header & __shapeText & __footer
RETURN __return
Color and shape changes:
Measure 2 =
VAR __shape =
IF(HASONEVALUE(Shapes[Shape]),MAX(Shapes[Shape]),"Circle")
VAR __indicator = SUM([Column1])
VAR __radius = 9
VAR __opacity = 0.75
VAR __color =
SWITCH(
TRUE(),
__indicator<=-5,"DarkOrchid",
__indicator<=-4,"DarkBlue",
__indicator<=-3,"Blue",
__indicator<=-2,"FireBrick",
__indicator<=-1,"Red",
__indicator=0,"Gold",
__indicator>=5,"HotPink",
__indicator>=4,"Orange",
__indicator>=3,"Tomato",
__indicator>=2,"SpringGreen",
__indicator>=1,"Green",
"White"
)
VAR __header = "data:image/svg+xml;utf8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' width='50' height='20'>"
VAR __footer = "</svg>"
VAR __shapeTextCircle = "<circle cx='10' cy='10' r='" & __radius & "' fill='" & __color & "' fill-opacity='" & __opacity & "' />"
VAR __shapeTextDiamond = "<polygon points=""10,0 20,10, 10,20 0,10"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextTriangle = "<polygon points=""0,20 20,20 10,0"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextDot = "<circle cx='10' cy='10' r='" & 1 & "' fill='" & __color & "' fill-opacity='" & __opacity & "' />"
VAR __shapeTextSquare = "<polygon points=""0,20 0,0, 20,1 20,20"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextRect = "<polygon points=""0,20 0,0, 40,1 40,20"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextHex = "<polygon points=""10,0 20,4 20,16 10,20 0,16 0,4"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextOct = "<polygon points=""6,0 14,0 20,6 20,14 14,20 6,20 0,14 0,6"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextPent = "<polygon points=""10,0 20,8 16,20 4,20 0,8"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeTextStar = "<polygon points=""10,0 12,9 20,8 13,13 16,20 10,15 4,20 7,13 0,9 8,9"" style=""fill:" & __color & ";stroke:" & __color & ";stroke-width:0;fill-rule:evenodd;"" />"
VAR __shapeText =
SWITCH(
TRUE(),
__indicator<=-5,__shapeTextCircle,
__indicator<=-4,__shapeTextDot,
__indicator<=-3,__shapeTextHex,
__indicator<=-2,__shapeTextOct,
__indicator<=-1,__shapeTextPent,
__indicator=0,__shapeTextRect,
__indicator>=5,__shapeTextSquare,
__indicator>=4,__shapeTextTriangle,
__indicator>=3,__shapeTextStar,
__indicator>=2,__shapeTextDiamond,
__indicator>=1,__shapeTextCircle,
__shapeTextCircle
)
VAR __return = __header & __shapeText & __footer
RETURN __return
NOTE: In the __header VAR, you need to change the : to an actual colon ( : )
eyJrIjoiMzI4Mjc5MWUtNjA5OS00ZmRmLTkzZGItZWI4MzFjMGQwMjgyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Thanks Greg for mentioning my article and also for all the helpful info in yours. Below also including the link to the color indicator article as the one you have is for the custom aggregations. I totally made it confusing as I published two different articles with the same name and adding part 1 and part 2 in the end 🙂
Cheers,
Diana
Appreciate your input @Dianapo! I really have gotten a ton of mileage out of your article. I've used that technique on a number of projects. Really appreciate your contributions to the community!