topic Re: Sharing: Creat your own visuals with DAX+SVG in DAX Commands and Tips

Sharing: Creat your own visuals with DAX+SVG

wujunmin — Thu, 05 Aug 2021 02:56:51 GMT

Mini Charts for Table and Matrix

1.bar

Bar = VAR W1=MAXX(ALL('table'),[data])/100 VAR W2=[data]/W1 VAR Color=IF([data]<100,"#B7472A","#217346") RETURN "data&colon;image/svg+xml;utf8,"&"<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'> <rect width="&"'"&W2&"'"&" height='40'" & " fill="&"'"&Color&"'"&"/>"&" <text x='0' y='60' fill='black' text-anchor='left' font-size='20'>"&[data]&"</text> </svg> "

2.Circle

Circle= VAR W1=MAXX(ALL('table'),[data])/40 VAR W2=[data]/W1 VAR Color=IF([data]<100,"#B7472A","#217346") RETURN "data&colon;image/svg+xml;utf8,"&"<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'> <circle cx='50' cy='50' r="& "'"&W2&"'" &" fill="&"'"&Color&"'"&"/>"&" <text x='50' y='50' fill='black' text-anchor='middle' font-size='20'>"&[data]&"</text> </svg> "

3. Stacked Bar

Stacked Bar = VAR W1=IF([data%]<0,0,IF([data%]>1,100,[data%]*100)) VAR Color=IF([data%]<1,"#B7472A","#217346") RETURN "data&colon;image/svg+xml;utf8,"&"<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100'> <rect x='0' y='30' width="&"'"&W1&"'"&" height='40'" & " fill="&"'"&Color&"'"&"/>"&" <rect x="&"'"&W1&"'"&" y='30' width="&"'"&100-W1&"'"&" height='40'" & " fill='Grey' />"&" <text x='0' y='60' fill='black' text-anchor='left' font-size='20'>"&FORMAT([data%],"Percent")&"</text> </svg> "

Re: Sharing: Creat your own visuals with DAX+SVG

wujunmin — Thu, 05 Aug 2021 02:21:50 GMT

Bar

1.Bar with two data labels

BAR = VAR CityCount = DISTINCTCOUNT ( 'Table'[City] ) VAR Height = IF ( CityCount <= 5, 20, INT ( DIVIDE ( 100, CityCount ) ) ) VAR MaxSales = MAXX ( VALUES ( 'Table'[City] ), [data] ) / 100 VAR BarTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[City], "Color", IF ( [data%] < 1, "Tomato", "DarkCyan" ), "Index", RANKX ( ALLSELECTED ( 'Table' ), [data],,, DENSE ) ), "Rect", "<rect x='0' y='" & ( [Index] - 1 ) * Height & "' width='" & [data] / MaxSales & "' height='" & Height & " ' fill='" & [Color] & " '/>", "Text", "<text x='0' y='" & ( [Index] - 1 ) * Height + INT ( Height * 0.6 ) & "' fill='black' text-anchor='left' font-size='" & INT ( Height * 0.6 ) & "' >" & [City] & "[" & ROUND ( [data%] * 100, 0 ) & "%][" & ROUND ( [data], 0 ) & "]" & "</text>" ) VAR Bar = CONCATENATEX ( BarTable, [Rect] & [Text] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100' >" & Bar & "</svg>" RETURN SVG

2. Bar with Round Angle

BAR = VAR CityCount = DISTINCTCOUNT ( 'Table'[City] ) VAR Height = IF ( CityCount <= 5, 20, INT ( DIVIDE ( 100, CityCount ) ) ) VAR MaxSales = MAXX ( VALUES ( 'Table'[City] ), [Data] ) / 100 VAR BarTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[City], "Color", IF ( [Data%] < 1, "Tomato", "DarkCyan" ), "Index", RANKX ( ALLSELECTED ( 'Table' ), [Data],,, DENSE ) ), "Rect", "<rect x='0' y='" & ( [Index] - 1 ) * Height & "' width='" & [Data] / MaxSales & "' height='" & Height & " ' rx='5' ry='5' fill='" & [Color] & "' />", "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 ( [Data], 0 ) & "]" & "</text>" ) VAR Bar = CONCATENATEX ( BarTable, [Rect] & [Text] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100' >" & Bar & "</svg>" RETURN SVG

Re: Sharing: Creat your own visuals with DAX+SVG

wujunmin — Thu, 05 Aug 2021 02:31:47 GMT

lollipop

lollipop1 = VAR CityCount = DISTINCTCOUNT ( 'Table'[City] ) VAR Height = IF ( CityCount <= 5, 20, INT ( DIVIDE ( 100, CityCount ) ) ) VAR MaxSales = MAXX ( VALUES ( 'Table'[City] ), [Data] ) / 95 VAR BarTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[City], "Color", IF ( [Data%] < 1, "Tomato", "DarkCyan" ), "Index", RANKX ( ALLSELECTED ( 'Table' ), [Data],,, DENSE ) ), "Rect", "<rect x='0' y='" & ( [Index] - 1 ) * Height + 1 & "' width='" & [Data] / MaxSales & "' height='1' fill='DarkCyan' />", "Circle", "<circle cx='" & [Data] / MaxSales & "' cy='" & ( [Index] - 1 ) * Height + 1.5 & "' r='2' fill='" & [Color] & "'/>", "Text", "<text x='0' y='" & ( [Index] - 1 ) * Height + INT ( Height * 0.6 ) + 2 & "' fill='black' text-anchor='left' font-size='" & INT ( Height * 0.6 ) & "' >" & [City] & "[" & ROUND ( [Data], 0 ) & "]" & "</text>" ) VAR Bar = CONCATENATEX ( BarTable, [Rect] & [Circle] & [Text] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100' >" & Bar & "</svg>" RETURN IF ( HASONEVALUE ( 'Table'[Province] ), SVG, BLANK () )

lollipop2 = VAR CityCount = DISTINCTCOUNT ( 'Table'[City] ) VAR Height = IF ( CityCount <= 5, 20, INT ( DIVIDE ( 100, CityCount ) ) ) VAR MaxSales = MAXX ( VALUES ( 'Table'[City] ), [Data] ) / 95 VAR BarTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[City], "Color", IF ( [Data%] < 1, "Tomato", "DarkCyan" ), "Index", RANKX ( ALLSELECTED ( 'Table' ), [Data],,, DENSE ) ), "Rect", "<rect x='0' y='" & ( [Index] - 1 ) * Height + 1 & "' width='" & [Data] / MaxSales & "' height='1' fill='" & [Color] & "' />", "Circle", "<circle cx='" & [Data] / MaxSales & "' cy='" & ( [Index] - 1 ) * Height + 1.5 & "' r='2' fill='" & [Color] & "'/>", "Text", "<text x='0' y='" & ( [Index] - 1 ) * Height + INT ( Height * 0.6 ) + 2 & "' fill='black' text-anchor='left' font-size='" & INT ( Height * 0.6 ) & "' >" & [City] & "[" & ROUND ( [Data], 0 ) & "]" & "</text>" ) VAR Bar = CONCATENATEX ( BarTable, [Rect] & [Circle] & [Text] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='100' width='100' >" & Bar & "</svg>" RETURN IF ( HASONEVALUE ( 'Table'[Province] ), SVG, BLANK () )

Re: Sharing: Creat your own visuals with DAX+SVG

wujunmin — Thu, 05 Aug 2021 02:50:39 GMT

Dumbbell Chart

Green: this year

Orange: last year

Black line: up

Gray line: down

Dumbbell = VAR ItemCount = DISTINCTCOUNT ( 'Table'[Store] ) VAR Height = 20 VAR MAX1 = MAXX ( ALL ( 'Table' ), [M.ThisYear] ) VAR MAX2 = MAXX ( ALL ( 'Table' ), [M.LastYear] ) VAR W = IF ( MAX1 >= MAX2, MAX1 / 100, MAX2 / 100 ) VAR Title_Width = 42 VAR DumbbellTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Store], "Color_Line", IF ( [M.ThisYear] <= [M.LastYear], "DarkGrey", "DimGray" ), "Value_Left", MIN ( [M.ThisYear], [M.LastYear] ), "Value_Right", MAX ( [M.ThisYear], [M.LastYear] ), "Color_Circle_Left", IF ( [M.ThisYear] <= [M.LastYear], "DarkCyan", "Tomato" ), "Color_Circle_Right", IF ( [M.ThisYear] > [M.LastYear], "DarkCyan", "Tomato"), "Index", RANKX ( ALLSELECTED ( 'Table' ), [M.ThisYear],,, DENSE ) ), "Line", "<line x1='" & Title_Width + [Value_Left] / W & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [Value_Right] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "' style='stroke:" & [Color_Line] & ";stroke-width:1' />", "Circle_Left", "<circle cx='" & Title_Width + [Value_Left] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "' r='1.5'" & " fill=" & "'" & [Color_Circle_Left] & "'/>", "Circle_Right", "<circle cx='" & Title_Width + [Value_Right] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "' r='1.5'" & " fill=" & "'" & [Color_Circle_Right] & "'/>", "Text_Left", "<text x='" & Title_Width + [Value_Left] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "' fill='black' text-anchor='Middle' font-size='5'>" & [Value_Left] & "</text>", "Text_Right", "<text x='" & Title_Width + [Value_Right] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "' fill='black' text-anchor='Middle' font-size='5'>" & [Value_Right] & "</text>", "Title", "<text x='0' y='" & ( [Index] - 1 ) * Height + 12 & "' fill='black' text-anchor='Right' font-size='5' >" & [Store] & "</text>" ) VAR Bar = CONCATENATEX ( DumbbellTable, [Line] & [Circle_Left] & [Circle_Right] & [Text_Left] & [Text_Right] & [Title] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='" & ItemCount * Height & "' width='" & 120 + Title_Width & "' >" & Bar & "<line x1='" & Title_Width & "' y1='0'" & " x2='" & Title_Width & "' y2='" & ItemCount * Height & "' style='stroke:grey;stroke-width:0.1'/>" & "</svg>" RETURN SVG

Dumbbell+Bar Chart

DumbbellBarChart = VAR ItemCount = DISTINCTCOUNT ( 'Table'[Store] ) VAR Height = 20 VAR MAX1 = MAXX ( ALL ( 'Table' ), [M.ThisYear] ) VAR MAX2 = MAXX ( ALL ( 'Table' ), [M.LastYear] ) VAR W = IF ( MAX1 >= MAX2, MAX1 / 100, MAX2 / 100 ) VAR Title_Width = 42 VAR DumbbellTable = ADDCOLUMNS ( SUMMARIZE ( 'Table', 'Table'[Store], "Color_Line", IF ( [M.ThisYear] <= [M.LastYear], "DarkGrey", "DimGray" ), "Value_Left", MIN ( [M.ThisYear], [M.LastYear] ), "Value_Right", MAX ( [M.ThisYear], [M.LastYear] ), "Color_Circle_Left", IF ( [M.ThisYear] <= [M.LastYear], "DarkCyan", "Tomato" ), "Color_Circle_Right", IF ( [M.ThisYear] > [M.LastYear], "DarkCyan", "Tomato" ), "Index", RANKX ( ALLSELECTED ( 'Table' ), [M.ThisYear],,, DENSE ) ), "Line", "<line x1='" & Title_Width + [Value_Left] / W & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [Value_Right] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "' style='stroke:" & [Color_Line] & ";stroke-width:1' />", "Circle_Left", "<circle cx='" & Title_Width + [Value_Left] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "' r='1.5'" & " fill=" & "'" & [Color_Circle_Left] & "'/>", "Circle_Right", "<circle cx='" & Title_Width + [Value_Right] / W & "' cy='" & ( [Index] - 1 ) * Height + 10 & "' r='1.5'" & " fill=" & "'" & [Color_Circle_Right] & "'/>", "Text_Left", "<text x='" & Title_Width + [Value_Left] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "' fill='black' text-anchor='Middle' font-size='5'>" & [Value_Left] & "</text>", "Text_Right", "<text x='" & Title_Width + [Value_Right] / W & "' y='" & ( [Index] - 1 ) * Height + 8 & "' fill='black' text-anchor='Middle' font-size='5'>" & [Value_Right] & "</text>", "Title", "<text x='0' y='" & ( [Index] - 1 ) * Height + 12 & "' fill='black' text-anchor='Right' font-size='5' >" & [Store] & "</text>", "Rect", "<line x1='" & Title_Width & "' y1='" & ( [Index] - 1 ) * Height + 10 & "' x2='" & Title_Width + [M.ThisYear] / W & "' y2='" & ( [Index] - 1 ) * Height + 10 & "' style='stroke:RosyBrown;stroke-width:6;stroke-opacity:0.3' />" ) VAR Bar = CONCATENATEX ( DumbbellTable, [Line] & [Circle_Left] & [Circle_Right] & [Text_Left] & [Text_Right] & [Title] & [Rect] ) VAR SVG = "data&colon;image/svg+xml;utf8," & "<svg xmlns='http://www.w3.org/2000/svg' height='" & ItemCount * Height & "' width='" & 120 + Title_Width & "' >" & Bar & "<line x1='" & Title_Width & "' y1='0'" & " x2='" & Title_Width & "' y2='" & ItemCount * Height & "' style='stroke:grey;stroke-width:0.1'/>" & "</svg>" RETURN SVG