Add conditional formatting to sparkline by DAX
Measure1(mark it as image URL): Sparkline = VAR MaxValue = MAXX ( VALUES ( 'table'[month]), [KPI]) VAR MinValue= MINX ( VALUES ( 'table'[month]), [KPI]) VAR Max_Width= IF(MinValue<0,IF(MaxValue<0, ABS(MinValue),MaxValue-MinValue),MaxValue) VAR BarTable= ADDCOLUMNS( SUMMARIZE('table','table'[year],'table'[month], "Color",IF([KPI]=MaxValue,"Green",IF([KPI]=MinValue,"Red","Grey"))), "Rect", "<rect x='"&IF(MinValue>0,0, IF(MaxValue>0, IF([KPI]<0,120-120*(MaxValue-[KPI])/Max_Width,120*ABS(MinValue)/Max_Width),120-120*ABS([KPI])/Max_Width)) &"' y='" & ( [month] - 1 ) * 10 & "' width='" & 120*ABS([KPI]) / Max_Width & "' height='9' fill='" & [Color] & " '/>") Return "data:image/svg+xml;utf8,"&" <svg xmlns='http://www.w3.org/2000/svg' width='120' height='120' > <g transform='rotate(-90,60,60)'>"& CONCATENATEX(BarTable,[Rect])&"</g> </svg> "Matrix Bubble Chart By DAX
Mark the measure as Image URL： Chart = VAR MAXR=MAXX(ALL('table'),[data]) RETURN "data:image/svg+xml;utf8,"&" <svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'> <line x1='0' y1='50' x2='100' y2='50' stroke='black' stroke-width='0.5' /> <line x1='50' y1='0' x2='50' y2='100' stroke='black' stroke-width='0.5' /> <circle cx='50' cy='50' r='"&40*[data]/MAXR &"' fill='"&IF([data]>100,"Darkcyan","Tomato")&"'/> <text x='50' y='50' font-size='20' text-anchor='middle' dominant-baseline='middle'>"&[data]&"</text> </svg> "DIY Gradient Bar Chart by DAX+SVG
Measure: Bar = VAR CityCount = DISTINCTCOUNT ( 'table'[city] ) VAR Height =30 VAR MaxSales = MAXX ( ALLSELECTED('table'), [revenue] ) / 300 VAR Color= "<defs> <LinearGradient id='wu'> <Stop offset='0%' style='stop-color:White'/> <Stop offset='100%' style='stop-color:DarkCyan'/> </LinearGradient> </defs>" VAR BarTable = ADDCOLUMNS ( SUMMARIZE ( 'table', 'table'[city], "index", RANKX ( ALLSELECTED ( 'table' ), [revenue],,, DENSE ) ), "Rect", "<rect x='0' y='" & ( [index] - 1 ) * Height & "' width='" & [revenue] / MaxSales & "' height='" & Height-2 & " ' rx='15' ry='15' fill='url(#wu)' />", "Text", "<text x='2' y='" & ( [index] - 1 ) * Height + INT ( Height * 0.6 ) & "' fill='black' text-anchor='left' font-size='" & INT ( Height * 0.6 ) & "' >" & [city] & "[" & ROUND ( [revenue], 0 ) & "]" & "</text>" ) VAR Bar = CONCATENATEX ( BarTable, [Rect] & [Text] ) VAR SVG = "<svg xmlns='http://www.w3.org/2000/svg' height='"&Height*CityCount&"' width='300' >" &Color& Bar & "</svg>" RETURN IF ( HASONEVALUE ( 'table'[province] ), SVG, BLANK () )SVG icons in matrix not showing up
Hi all, I need some help with my matrix. For the matrix outlined in pink, the SVG icons do not show up 😞 The column [svg_url] has values formatted like this : "data:image/svg+xml;utf8," & "<svg fill='#4E8BA1' width='800px' height='800px' viewBox='0 0 1920 1920' xmlns='http://www.w3.org/2000/svg'><path d='M213.333 960c0-167.36 56-321.707 149.44-446.4L1406.4 1557.227c-124.693 93.44-279.04 149.44-446.4 149.44-411.627 0-746.667-335.04-746.667-746.667m1493.334 0c0 167.36-56 321.707-149.44 446.4L513.6 362.773c124.693-93.44 279.04-149.44 446.4-149.44 411.627 0 746.667 335.04 746.667 746.667M960 0C429.76 0 0 429.76 0 960s429.76 960 960 960 960-429.76 960-960S1490.24 0 960 0' fill-rule='evenodd'/></svg>" The data category is set as Image URL I've had success with the matrix outlined in yellow (using http links), but wanted to use a different method for linking svg icons as I wanted fuller control over the icons' colour. I've also tested out the same values using a DAX measure [svg_url_DAX] and displaying it as a table (outlined in green), it shows up just fine, I wonder what went wrong. 😬 Link to .pbix file + svg icons : https://drive.google.com/drive/folders/1d_6_pT5TJtg5KpRC79plvzXfako83upA?usp=sharing Appreciate any help and pointers! Much thanks!McKinsey‘s waffle chart by DAX
Mark this measure as ImageURL and put it in table or matrix Waffle = VAR t = GENERATESERIES ( 1, 10 ) VAR tPlus = GENERATE ( SELECTCOLUMNS ( t, "Value1", [Value] ), t ) VAR tPlusPlus = ADDCOLUMNS ( tPlus, "Index", RANKX ( tPlus, [Value] + [Value1] / 100,, ASC ) ) VAR tWaffle = ADDCOLUMNS ( tPlusPlus, "circle", "<circle cx='" & [Value] * 10 + 15 & "' cy='" & [Value1] * 10 + 15 & "' r='4' fill='" & IF ( [Index] <= ROUND ( [percent] * 100, 0 ), "Black", "LightGrey" ) & "' />" ) VAR Chart = "data:image/svg+xml;utf8," & " <svg xmlns='http://www.w3.org/2000/svg' width='150' height='150'>" & " <g transform='rotate(-90,75,75)'>" & CONCATENATEX ( tWaffle, [circle] ) & " </g> <text x='70' y='20' font-size='15' text-anchor='middle' >" & ROUND ( [percent] * 100, 0 ) & "</text> <text x='70' y='148' font-size='15' text-anchor='middle' >" & SELECTEDVALUE ( 'table'[store] ) & "</text> </svg> " RETURN IF ( HASONEVALUE ( 'table'[store] ), Chart, BLANK () )Add any chart/image to map by DAX
How? First, you need a SVG map, you could get them from https://www.amcharts.com/svg-maps/ Download the map and copy the code to Power BI. Add a new measure, paste the SVG code: The map could be shown by HTML Content: Second, you need XY Value for every location. Third, creat the chart measure: Column_Chart_Map= VAR MAX_Value = MAXX ( ALL ( 'table'[Province] ), [value] ) VAR MAX_Width = 40 VAR FontSize = 12 VAR Chart = CONCATENATEX ( 'table', "<text x='" & [坐标轴_X] & "' y='" & [坐标轴_Y] + FontSize + 1 & "' text-anchor='start' font-size='" & FontSize & "' fill='Grey'>" & [Province] & "</text>" & "<rect rx='1' x='" & [坐标轴_X] - 5 & "' y='" & [坐标轴_Y] + FontSize + 1 - MAX_Width * [value] / MAX_Value & "'height='" & MAX_Width * [value] / MAX_Value & "'width='5' fill='" & IF ( [value] >= 50, "DarkCyan", "Tomato" ) & "'/>" ) RETURN SUBSTITUTE ( [MAP], "</svg>", Chart & "</svg>" ) Image_Map= VAR MAX_Value = MAXX ( ALL ( 'table'[Province] ), [value] ) VAR MAX_Width = 40 VAR FontSize = 12 VAR image = "http://*****.com/****.png VAR imagemap = CONCATENATEX ( 'table', "<image xlink:href='" & image & "' x='" & [坐标轴_X] & "' y='" & [坐标轴_Y] & "' width='" & MAX_Width * [value] / MAX_Value & "' height='" & MAX_Width * [value] / MAX_Value & "'></image>" & "<text x='" & [坐标轴_X] & "' y='" & [坐标轴_Y] + FontSize + 1 & "' text-anchor='start' font-size='" & FontSize & "' fill='Black'>" & [Province] & "</text>" ) RETURN SUBSTITUTE ( [MAP], "</svg>", imagemap & "</svg>" ) Again, you could see the result by HTML content.Add animation to sparkline by DAX
Measure( mark it as image URL) : sparkline = VAR MaxValue = MAXX ( VALUES ( 'table'[month]), [Value]) VAR BarTable= ADDCOLUMNS( SUMMARIZE('table','table'[year],'table'[month], "Color","DarkCyan"),// Conditional format "Rect", "<rect x='"& ([month] - 1 ) * 10 &"' y='0' height='" & 120*[Value] / MaxValue & "' width='10' fill='" & [Color] & "'> <animate attributeName='y' from='"&-120*[Value] / MaxValue&"' to='0' begin='0s' dur='3s' repeatCount='indefinite'/> </rect>") Return "data:image/svg+xml;utf8,"&" <svg xmlns='http://www.w3.org/2000/svg' viewbox='0 0 120 120'> "& CONCATENATEX(BarTable,[Rect])&" <text x='60' y='30' font-size='20' text-anchor='middle' dominant-baseline='middle' fill='#F4F5F9'>"&FORMAT([Value],"0,000")&"</text> </svg> "Dumbbell bar chart in table and matrix
Result: Mark the SVG measure below as image URL： 表格哑铃图 = VAR MAX1 = MAXX ( ALL ( 'table' ), [M.今年] ) VAR MAX2 = MAXX ( ALL ( 'table' ), [M.去年] ) VAR W = IF ( MAX1 >= MAX2, MAX1, MAX2) VAR Value_Left= MIN ( [M.今年], [M.去年] ) VAR Value_Right=MAX ( [M.今年], [M.去年] ) VAR Color_Circle_Left=IF ( [M.今年] <= [M.去年], "DeepSkyBlue", "Black" ) VAR Color_Circle_Right=IF ( [M.今年] > [M.去年], "DeepSkyBlue", "Black") VAR Line="<line x1='" & Value_Left / W*130+10 & "' y1='15' x2='" & Value_Right / W*130+10 & "' y2='15' style='stroke:DimGray;stroke-width:2' />" VAR Circle_Left= "<circle cx='" & Value_Left / W*130+10 & "' cy='15' r='5'" & " fill='" & Color_Circle_Left & "'/>" VAR Circle_Right= "<circle cx='" & Value_Right / W*130+10 & "' cy='15' r='5'" & " fill='" & Color_Circle_Right & "'/>" VAR Bar = Line& Circle_Left & Circle_Right VAR SVG = "data:image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='30' width='150' >" & Bar & "</svg>" RETURN SVGApple watch's donut chart by DAX
One day I saw apple's ads, these donut charts are amazing: So, I put one of them (right one) in Power BI by DAX： Here is the measure: Chart = VAR Pct=0.66 // repalce 0.66 with your percent measure VAR SVG= "<svg viewbox='0 0 110 60' xmlns='http://www.w3.org/2000/svg'> <circle cx='55' cy='55' r='50' fill='none' stroke='LightGrey' stroke-opacity='0.4' stroke-width='10' stroke-linecap='round' stroke-dasharray='" & PI()*50 & "' stroke-dashoffset='" & PI()*50 & "'/> <g id='wujunmin'> <circle cx='55' cy='5' r='5' fill='White' fill-opacity='0.8' stroke='#228FF1' stroke-width='2.5'/> <animateTransform attributeName='transform' type='rotate' from='-90 55 55' to='"&180*pct-90 &" 55 55' begin='0s' dur='3s' fill='freeze'/> </g> <text x='55' y='45' text-anchor='middle' dominant-baseline='middle' font-size='25' fill='white'>"&ROUND(Pct*100,0)&"</text> </svg>" RETURN SVG You need HTML Content to get the result: This chart even has some kind of animation:Triangle style bar chart by DAX+SVG
Result: Mark the measure below as image URL: Chart = VAR Max_Value = MAXX ( ALLSELECTED ('table'),[KPI] ) VAR SVG =" data:image/svg+xml;utf8, <svg xmlns='http://www.w3.org/2000/svg' width='300' height='30'> <polygon fill='deepskyblue' points=' " & 300 * [KPI] / Max_Value & ",15 0,0 0,30' /> </svg>" RETURN SVG
