Skip to main content

One post tagged with "UDF"

View All Tags

Telecoms - Display Cell Tower KPIs in Icon Map Pro

· 6 min read
James Dales
James Dales
Co-founder of Tekantis

A common use case for our telecoms customers is to show cell tower locations, using wedge diagrams, to represent various information and KPIs. This blog shows a repeatable approach for achieving this using Icon Map Pro.

Icon Map Pro showing cell tower KPIs as wedges

Consider our source data:

SiteIDCellIDLatitudeLongitudeAzimuthBeamwidthThroughput
SITE_1CELL_1_251.5104471707975-0.14339976206510
SITE_1CELL_1_251.5104471707975-0.143399762906510
SITE_1CELL_1_351.5104471707975-0.143399762180906
SITE_1CELL_1_451.5104471707975-0.143399762270906
SITE_2CELL_2_151.519405119581-0.11369152904510
SITE_2CELL_2_251.519405119581-0.113691529909010
SITE_2CELL_2_351.519405119581-0.113691529180456
SITE_2CELL_2_451.519405119581-0.113691529270456
SITE_3CELL_3_151.5178896481135-0.14733269306010
SITE_3CELL_3_251.5178896481135-0.147332693906010
SITE_3CELL_3_351.5178896481135-0.147332693180906
SITE_3CELL_3_451.5178896481135-0.147332693270906
SITE_4CELL_4_151.5086784040137-0.13254363906510
SITE_4CELL_4_251.5086784040137-0.132543639180656
SITE_5CELL_5_151.5072231988425-0.14059565906010
SITE_5CELL_5_251.5072231988425-0.1405956591204510
SITE_5CELL_5_351.5072231988425-0.140595659240656
SITE_6CELL_6_151.5161939797486-0.1261331270906
SITE_6CELL_6_251.5161939797486-0.126133127120606
SITE_6CELL_6_351.5161939797486-0.126133127240906

We have 6 sites, each with a number of sectors (cells), located using latitude and longitude. Each sector has its azimuth (the central pointing direction of the antenna, measured clockwise from true north) and its beamwidth (the angular width of the main lobe). We also include the current throughput KPI for each cell. We’re going to use this throughput value to drive the wedge coloring.

To show the wedge symbols for each site, we're going to construct an SVG for each location. This is a great opportunity to use a new feature in Power BI - DAX User Defined Functions (UDFs). However, at the time of writing, this is a preview feature, so we need to ensure it's enabled in Power BI's preview features settings:

Power BI Preview Features Dialog Showing the Location of the User Defined Functions setting

You will need to restart Power BI Desktop after enabling this feature.

With this enabled, we can now use Power BI's DAX view to paste in our UDFs.

Location of DAX view in Power BI Desktop

Our main UDF generates a single wedge as an SVG path. We also have a couple of other functions to ensure our SVG is rendered correctly as a data URL to include in the map.

DEFINE
    FUNCTION RFWedgePath = (
    minRadius: DOUBLE, maxRadius: DOUBLE, azimuth: DOUBLE, beamWidth: DOUBLE,
    fill: STRING, stroke: STRING, strokeWidth: DOUBLE,
    vbw: DOUBLE, vbh: DOUBLE
  ) =>

VAR cx = DIVIDE(vbw, 2)
VAR cy = DIVIDE(vbh, 2)

VAR r0 = minRadius
VAR r1 = maxRadius

VAR a0 = azimuth - beamWidth / 2
VAR a1 = azimuth + beamWidth / 2

VAR rad0 = RADIANS(a0)
VAR rad1 = RADIANS(a1)

VAR x0o = cx + r1 * COS(rad0)
VAR y0o = cy - r1 * SIN(rad0)
VAR x1o = cx + r1 * COS(rad1)
VAR y1o = cy - r1 * SIN(rad1)

VAR sweep    = 0
VAR largeArc = IF( ABS(a1 - a0) > 180, 1, 0 )

VAR dPath =
    IF(
      r0 <= 0,
      "M " & NumText(x0o) & " " & NumText(y0o) &
      " A " & NumText(r1) & " " & NumText(r1) & " 0 " & largeArc & " " & sweep & " " &
             NumText(x1o) & " " & NumText(y1o) &
      " L " & NumText(cx)  & " " & NumText(cy) & " Z",
      VAR x1i = cx + r0 * COS(rad1)
      VAR y1i = cy - r0 * SIN(rad1)
      VAR x0i = cx + r0 * COS(rad0)
      VAR y0i = cy - r0 * SIN(rad0)
      RETURN
      "M " & NumText(x0o) & " " & NumText(y0o) &
      " A " & NumText(r1) & " " & NumText(r1) & " 0 " & largeArc & " " & sweep & " " &
             NumText(x1o) & " " & NumText(y1o) &
      " L " & NumText(x1i) & " " & NumText(y1i) &
      " A " & NumText(r0) & " " & NumText(r0) & " 0 " & largeArc & " " & (1 - sweep) & " " &
             NumText(x0i) & " " & NumText(y0i) & " Z"
    )

RETURN
  "<path d='" & dPath & "' fill='" & fill & "' stroke='" & stroke &
  "' stroke-width='" & NumText(strokeWidth) & "' />"

DEFINE
    FUNCTION NumText = (n: DOUBLE) =>
VAR t = FORMAT(n,"0.########")
RETURN IF(RIGHT(t,1)=".", LEFT(t,LEN(t)-1), t)


DEFINE
    FUNCTION UrlEncodeSvg = ( raw : STRING ) =>
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(
                            raw,
                            " ", "%20"
                        ),
                        "#", "%23"
                    ),
                    """", "%22"
                ),
                "<", "%3C"
            ),
            ">", "%3E"
        ),
        "'", "%27"
    )

Paste this into the DAX query window and press the "Update model with changes (3)" button.

Screenshot showing the Update model with changes button

We now have a reusable set of functions that we can call from inside a DAX measure.

However, before we generate our tower SVG, we're going to create a quick DAX measure to determine the wedge colors based on the Throughput value.

Add the following measure:

KPI Color = 
VAR t = SELECTEDVALUE('Cells'[Throughput])
RETURN
SWITCH(
    TRUE(),
    ISBLANK(t),              "#BDBDBD",   -- no data: grey
    t <= 2,                  "#d73027",   -- red
    t <= 4,                  "#fc8d59",
    t <= 6,                  "#fee08b",
    t <= 8,                  "#d9ef8b",
    t <= 10,                 "#91cf60",
                              "#1a9850"   -- >10: green
)

Now we're ready to add our measure to generate the SVGs. This measure will aggregate the cells belonging to a site and generate a combined SVG symbol for that site:

Site Symbol = 
VAR width       = 300
VAR height      = 300
VAR strokeColor = "#000000"
VAR strokeWidth = 4
VAR minRadius   = 18
VAR maxRadius   = 140

VAR paths =
    CONCATENATEX(
        'Cells',       
        RFWedgePath(
            minRadius, maxRadius,
            'Cells'[Azimuth],
            'Cells'[Beamwidth],
            [KPI Color],   
            strokeColor, strokeWidth,
            width, height
        ),
        ""        
    )

RETURN
"data:image/svg+xml;utf8," &
UrlEncodeSvg(
    "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " & NumText(width) & " " & NumText(height) & "'>" &
      paths &
    "</svg>"
)

It generates a series of paths, one for each cell, and then wraps them up in an SVG tag. We then use our new UrlEncodeSvg function to URL Encode the SVG and create it as a data URL.

We're now ready to configure Icon Map Pro to show our cell tower locations.

The data configuration is straightforward. The ID field in Icon Map Pro should represent the granularity of each item we want on the map, which in this case is a cell tower site, so add the Site ID to Icon Map Pro's ID field. Then drag in the longitude and latitude. Ensure the aggregation type is Average. This will make sure the longitude and latitude stays the same for each site. Then drag our new Site Symbol measure to the Image / WKT field:

Icon Map Pro field configuration

The final step is to turn on the Image Data Layer in Icon Map Pro's settings:

Icon Map Pro Settings configuration

Your map should now look like this:

Resulting Map

You can download the .pbix file to see this in action.

These same functions can be used to draw more complicated diagrams by varying the min and max radius settings:

More complicated examples