Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
wujunmin
Advocate I
Advocate I

Sharing: Creat your own visuals with DAX+SVG

‎08-04-2021 07:03 PM

Mini Charts for Table and Matrix

1.bar

wujunmin_0-1628128170826.png

 

 

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

wujunmin_1-1628128330158.png

 

 

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

wujunmin_2-1628128512849.png

 

 

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> "

 

 

 

 

Labels:
Message 1 of 4
1,446 Views
0
3 REPLIES 3
wujunmin
Advocate I
Advocate I

‎08-04-2021 07:49 PM

Dumbbell Chart

Green: this year

Orange: last year

Black line: up

Gray line: down

wujunmin_0-1628131293664.png

 

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

wujunmin_1-1628131569825.png

 

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

 

 

Message 4 of 4
1,414 Views
0
wujunmin
Advocate I
Advocate I

‎08-04-2021 07:31 PM

lollipop

1.

wujunmin_2-1628130399060.png

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 () )

 

2.

wujunmin_3-1628130602231.png

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 () )
Message 3 of 4
1,418 Views
0
wujunmin
Advocate I
Advocate I

‎08-04-2021 07:21 PM

Bar

1.Bar with two data labels

wujunmin_0-1628129629991.png

 

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

wujunmin_1-1628129858524.png

 

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

 

 

Message 2 of 4
1,419 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All