Build Your Custom Visuals in Power BI (Step-by-Step)

Uncategorized

By Tsuyoshi Matsuzaki on 2016-09-27 • ( 26 Comments )

In this blog post, I show you how to create your own custom visuals in Power BI.
You can easily create your own visuals and put them together with the existing visuals in your report. You can also submit your visual into Office Store (Seller Dashboard) for publishing.

Note : I described about custom visuals in the previous post “How to create Power BI custom visuals (Japanese)“, but the new development tool (the following command-line tool) is released and generally available.
From now on, please use this new tool.

Setup your dev env

For custom visual development, all you have to provision (install) is as follows. Nothing else is needed ! (In this post, we run and setup on Windows.)

• PC (Windows), Mac OS, or Linux
• Node.js
• openssl (You don’t need openssl in Windows 8 or above, since Powershell is used instead.)
• Power BI service account (free)

Before development, you must install the Power BI visual tool (command-line tool) which is used for all development tasks.

npm install -g powerbi-visuals-tools

As I show you later, your web browser connects to your local server (https://localhost:8080/) within Power BI service (https://app.powerbi.com/) for debugging your custom visuals. To enable this debugging, you should generate and install a local server certificate for SSL by running the following command.
By running this command, import the current user certificate (a generated certificate) into “Trusted Root Certification Authorities” store.

pbiviz --install-cert

Note : If this command fails for some reason, generate and install a certificate manually.
For instance, when you’re running on Windows 10, run the following command in Powershell to generate a self-signed certificate.

$cert = New-SelfSignedCertificate `
  -DnsName localhost `
  -HashAlgorithm sha256 `
  -Type Custom `
  -Subject localhost `
  -KeyAlgorithm RSA `
  -KeyLength 2048 `
  -KeyExportPolicy Exportable `
  -CertStoreLocation Cert:\\CurrentUser\\My `
  -NotAfter (get-date).AddDays(180)
$thumbprint = $cert.Thumbprint.toString()
$certname = 'Cert:\\CurrentUser\\My\\' + $thumbprint
Export-PfxCertificate -Cert $certname `
  -FilePath $env:appdata\npm\node_modules\powerbi-visuals-tools\certs\PowerBICustomVisualTest_public.pfx `
  -Password (ConvertTo-SecureString -String 'P@ssw0rd' -Force -AsPlainText)

Double-click a generated pfx (in %appdata%\npm\node_modules\powerbi-visuals-tools\certs), and then import this cert into “Trusted Root Certification Authorities” store.
Finally replace “pfx” value into your cert file’s relative path name (“certs/PowerBICustomVisualTest_public.pfx” in above) and “passphrase” value into cert’s password (“P@ssw0rd” in above) in %appdata%\npm\node_modules\powerbi-visuals-tools\config.json file.

Create New Project

Next, you create the new custom visual project using “pbiviz” command previously installed.
Here we create the project named “MyTestVisual” as follows.

pbiviz new myTestVisual
cd myTestVisual
npm install

The folder “myTestVisual” is created, and all the project files are located in this folder.
You can change these source code as you like.

Anyway, Run and Test !

The source code is written by the typescript.
Please see the source code of src/visual.ts in your project folder. This is the main logic of your custom visuals, and it’s just displaying the simple text as follows. (When the “update” method is called for re-drawing, the “updateCount” is incremented each time.)

module powerbi.extensibility.visual {
  export class Visual implements IVisual {
    private target: HTMLElement;
    private updateCount: number;

    constructor(options: VisualConstructorOptions) {
      console.log('Visual constructor', options);
      this.target = options.element;
      this.updateCount = 0;
    }

    public update(options: VisualUpdateOptions) {
      console.log('Visual update', options);
      this.target.innerHTML = '<p>Update count: <em>${(this.updateCount++)}</em></p>';
    }

    public destroy(): void {
      //TODO: Perform any cleanup tasks here
    }
  }
}

Now let’s look at this sample in your Power BI !
Move to the visual project folder (which contains pbiviz.json), and please type the following command.
This launches the http server (https://localhost:8080/), and starts (hosts) this visual in this server.

pbiviz start

Open and login to Power BI service (https://app.powerbi.com). Then select [Settings] menu like the following screenshot.

When the setting page is displayed, select [Enable developer visual for testing].

This enables developer visual in your visual pane. After you enable this, you can see the following icon (Developer Visual icon) in the visual pane on Power BI service.

When you insert this visual into your page, the local visual (which is hosted in https://localhost:8080/) is displayed like the following screenshot.
If you resize this visual, you can see that the count is incremented by re-drawing.

Note : If you see the error like the following screenshot, the server certificate (SSL certificate) is not installed in your client machine. See above to install a server certificate.

The “pbiviz” (Visual Builder) is having the built-in file watcher. If you change your source code, it detects your changes and the source code is compiled (re-built) soon. (You can check the work in Power BI service immediately.)

Build your own visuals !

Super, Super, Basic

Now let’s see the data structure of Power BI visuals inside.

In our example, we use the following data and then import the source data with this table into Power BI service.
(Here I created this table in Azure SQL Database, imported into Power BI Desktop, and published into my Power BI service.)

ItemName	Year	Country	Cost
Mouse	2014	Japan	25
Power Unit	2015	Japan	30
Accesory	2016	Japan	18
Cable	2015	North America	14
Mouse	2016	North America	30
Phone	2016	China	100

As I described above, the main class of your custom visual is in src/visual.ts and this class implements IVisual interface.
The important methods of your visual class (IVisual) is the constructor and “update” method. You must setup your visual in your constructor, and draw your visual in the “update” method.

The argument “options” is passed in “update” method, and options.dataViews includes all the data for your visualization. (You can take multiple dataViews, but, in this blog post, we assume a single dataView. Then we always refer as options.dataViews[0].)
The dataView is including the 5 properties of “metadata“, “categorical“, “matrix“, “table“, and “tree“. I’ll explain each of these in this section.

First, the “metadata” is including the meta information like “what properties are included”, “which type does the property have”, “what option is selected” (I’ll explain later for options) and so on.
Let’s change your code (src/visual.ts) as follows and start debugging (run “pbiviz start“).

export class Visual implements IVisual {
  private target: HTMLElement;

  constructor(options: VisualConstructorOptions) {
    var captionArea = document.createElement("div");
    captionArea.innerHTML = "This is test chart";
    options.element.appendChild(captionArea);
    this.target = document.createElement("div");
    options.element.appendChild(this.target);
  }

  public update(options: VisualUpdateOptions) {
    this.target.innerHTML =
      JSON.stringify(options.dataViews[0].metadata.columns);
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }
}

When you insert data field (ItemName, Year, Country, Cost) into “Categorical Data” or “Measure Data” in your visual, you will see output like the following screenshot. (You can see the json raw string of options.dataViews[0].metadata.columns as a plain text.)

The other properties (“categorical“, “matrix“, “table“, and “tree“) are all including data itself. These data has same values (in this case, the above 6 rows of data), but its representation is different each other.
In this post, I show you the example of “categorical” and “table” (which is frequently used) as follows.

When we assume above 6 rows of data, the “categorical” property includes the following representation of data. (As I’ll describe later, this expression depends on the capabilities definition. This is one of these examples.)

{
  "categories": [
    {
      "source":{
        "displayName": "Country"
      }
      "values":[
        "Japan",
        "Japan",
        "Japan",
        "North America",
        "North America",
        "China"
      ]
    },
    {
      "source":{
        "displayName": "Year"
      }
      "values":[
        "2014",
        "2015",
        "2016",
        "2015",
        "2016",
        "2016"
      ]
    }
  ],
  "values":[
    {
      "source":{
        "displayName": "Cost"
      }
      "values":[
        "25",
        "30",
        "18",
        "14",
        "30",
        "100"
      ]
    }
  ]
}

On contrary, the “table” property represents as follows for same data.

{
  "columns":[
    {"displayName": "Year"},
    {"displayName": "Country"},
    {"displayName": "Cost"}
  ],
  "rows":[
    [2014, "Japan", 25],
    [2015, "Japan", 30],
    [2016, "Japan", 18],
    [2015, "North America", 14],
    [2016, "North America", 30],
    [2016, "China", 100]
  ]
}

If you want to see these raw data (“metadata“, “categorical“, “matrix“, “table“, and “tree“) in development-time, you can use the following button in the Developer Visual. (When you click the following “Show Dataview” button, you can see raw json data in developer visual as follows.)

dataRoles and dataViewMappings

Actually, the previous data representation is determined by your definition called “capabilities”.
Open the capabilities.json in your project root folder, and please update this json as follows. (The property “kind” indicates that “0” is “grouping data” and “1” is “measuring data”.)

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": "Grouping"
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": "Measure"
    }
  ],
  "dataViewMappings": [
    {
      "categorical": {
        "categories": {
          "for": {
            "in": "myCategory"
          },
          "dataReductionAlgorithm": {
            "top": {}
          }
        },
        "values": {
          "select": [
            {
              "bind": {
                "to": "myMeasure"
              }
            }
          ]
        }
      }
    }
  ]
}

If you insert this visual in your page, you can see the visual pane like the following screenshot. As you can see, the defined dataRoles (in this case, “Summarize Category” and “Measure Data”) in above is shown in the visual pane.

Please drag the following “Country” and “Cost” from the data source.

Then you can get the following json as options.dataViews[0].categorical in the “update” method. (Note that options.dataViews[0].categorical doesn’t return the following string, but you can retrieve using each elements, such as, options.dataViews[0].categorical.categories and options.dataViews[0].categorical.values.)
As you can see, the following data is summarized by “Country“.

{
  "categories": [
    {
      "source": {
        "roles": {
          "myCategory": true
        },
        "displayName": "Country",
        "index": 0,
        ...
      },
      "values": [
        "China",
        "Japan",
        "North America"
      ],
      "identity": [...],
      "identityFields": [...]
    }
  ],
  "values": [
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        ...
      },
      "values": [
        100,
        73,
        44
      ],
      "minLocal": 44,
      "maxLocal": 100
    }
  ]
}

Next example shows how you can use “group by” in your capabilities.json.

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": "Grouping"
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": "Measure"
    },
    {
      "displayName": "Grouping",
      "name": "myGroup",
      "kind": "Grouping"
    }
  ],
  "dataViewMappings": [
    {
      "categorical": {
        "categories": {
          "for": {
            "in": "myCategory"
          },
          "dataReductionAlgorithm": {
            "top": {}
          }
        },
        "values": {
          "group": {
            "by": "myGroup",
            "select": [
              {
                "for": {
                  "in": "myMeasure"
                }
              }
            ]
          }
        }
      }
    }
  ]
}

If you insert this visual in your page, you can see the visual pane like the following screenshot. Here we can see “Grouping” area in the visual pane.

If you select “Year” as grouping data, you can see the following dataView result as the categorical data.
As you can see, the result data is expressed as “Cost” data of 2 dimensions of categories (“Country” and “Year“).

{
  "categories": [
    {
      "source": {
        "roles": {
          "myCategory": true
        },
        "displayName": "Country",
        "index": 0,
        ...
      },
      "values": [
        "China",
        "Japan",
        "North America"
      ],
      "identity": [...],
      "identityFields": [...]
    }
  ],
  "values": [
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2014,
        ...
      },
      "values": [
        { },
        25,
        { }
      ],
      "identity": {...}
    },
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2015,
        ...
      },
      "values": [
        { },
        30,
        14
      ],
      "identity": {...}
    },
    {
      "source": {
        "roles": {
          "myMeasure": true
        },
        "displayName": "Cost",
        "index": 1,
        "isMeasure": true,
        "groupName": 2016,
        ...
      },
      "values": [
        100,
        18,
        30
      ],
      "identity": {...}
    }
  ]
}

If you want to get data as “table” (not “categorical“), you should write as follows in the capabilities.json.

{
  "dataRoles": [
    {
      "displayName": "Summarize Category",
      "name": "myCategory",
      "kind": "Grouping"
    },
    {
      "displayName": "Measure Data",
      "name": "myMeasure",
      "kind": "Measure"
    }
  ],
  "dataViewMappings": [
    {
      "table": {
        "rows": {
          "select": [
            { "for": { "in": "myCategory" } },
            { "for": { "in": "myMeasure" } }
          ]
        }
      }
    }
  ]
}

If you select “Country” and “Cost” as input data, you can get the following data in options.dataViews[0].table. (Note that options.dataViews[0].table doesn’t return the following string, but you can retrieve each element like options.dataViews[0].table.columns, options.dataViews[0].table.rows, and so on.)

{
  "columns": [
    {
      "roles": {
        "myCategory": true
      },
      "displayName": "Country",
      "index": 0,
      ...
    },
    {
      "roles": {
        "myMeasure": true
      },
      "displayName": "Cost",
      "index": 1,
      "isMeasure": true,
      ...
    }
  ],
  "rows": [
    [
      "China",
      100
    ],
    [
      "Japan",
      73
    ],
    [
      "North America",
      44
    ]
  ],
  "identity": [...],
  "identityFields": [...]
}

From now, my following descriptions use this table format of data.

See document in GitHub repo for details about dataRoles and DataViewMappings.

PowerBI-visuals : DataViewMappings
https://github.com/Microsoft/PowerBI-visuals/blob/master/Capabilities/DataViewMappings.md

Custom Visual Properties

As you saw before, you can specify data roles using dataRoles definition and data structure with dataViewMappings definition in capabilities.json.
With capabilities.json, you can also add other custom properties in your visuals.

First, you define your custom object using “objects” and custom property using “properties” in capabilities.json as follows.

{
  "dataRoles": [
    ...
  ],
  "dataViewMappings": [
    ...
  ],
  "objects": {
    "myCustomObj": {
      "displayName": "Test Object",
      "properties": {
        "myprop": {
          "displayName": "Test Property",
          "type": { "bool": true }
        }
      }
    }
  }
}

After you modify capabilities.json, you must implement “enumerateObjectInstances” method in your visual class (src/visual.ts). In this method, the property value is populated by the variable (in this case, “this.myVisualProp“).
If the user changes this bool value, update() method will be called and the output is changed. Your code should save this value into the variable (this.myVisualProp) and do the appropriate task in update() method. (The following example is just showing the value in the visual UI.)

export class Visual implements IVisual {
  private target: HTMLElement;
  private myVisualProp: boolean;

  constructor(options: VisualConstructorOptions) {
    var captionArea = document.createElement("div");
    captionArea.innerHTML = "This is test chart";
    options.element.appendChild(captionArea);
    this.target = document.createElement("div");
    options.element.appendChild(this.target);

    this.myVisualProp = false;
  }

  public update(options: VisualUpdateOptions) {
    // This example just shows the selected property in visual.
    this.myVisualProp = options.dataViews[0].metadata.objects["myCustomObj"]["myprop"] as boolean;
    this.target.innerHTML =
      "Custom Prop is " + this.myVisualProp;
  }

  public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstance[] | VisualObjectInstanceEnumerationObject {
    let objectName = options.objectName;
    let objectEnumeration: VisualObjectInstance[] = [];

    switch (objectName) {
      case 'myCustomObj':
        objectEnumeration.push({
          objectName: objectName,
          properties: {
            myprop: this.myVisualProp,
          },
          selector: null
        });
        break;
    };

    return objectEnumeration;
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }
}

The result looks like as follows.

In this example we’ve created the simple bool property, but you can also use other types of properties including the color pallet like the following screenshot.

In this section I’ve just explained a simple custom property, but you can also support other advanced capabilities like “highlighting”. (The highlighting enables selected value highlighted and causes all other visuals on the page, as the built-in visual does. For the highlighting capabilities, see “PowerBI-visuals : Highlighting” for details.)

Visualize your data !

After you have determined the data format using dataViewMappings, now you can start to build your own visuals.

As I mentioned above, we use the previous table format in dataViewMappings here.
In this case, options.dataViews[0].table.columns returns in your visual as follows :

{
  "0":{
    "roles": {"myCategory": true},
    "displayName": "Country",
    ...
  },
  "1":{
    "roles": {"myMeasure": true},
    "displayName": "Cost",
    ...
  }
}

And options.dataViews[0].table.rows returns :

[
  ["China", 100],
  ["Japan", 73],
  ["North America", 44]
]

Now we start to draw our visual using d3 !

You can use your favorite external libraries for visualization (see “PowerBI-visuals – Adding External Libraries“), however, fortunately both d3.js and its type definition (@types/d3) for typescripts are already included in a project template by default.
Thus you don’t need to install anything else ! (Now it’s ready.)

You can implement your custom visuals (src/visual.ts) as follows using d3.js.

Notice that here we’re assuming the fixed label (“Country” for “Summarize Category”, “Cost” for “Measure Data”) for simplifying our source code in order to make you understand clearly. However, you should extract these values (user’s settings) from metadata (or column definitions) in real production.
You cannot also change the fill-in color, background color, so on and so forth in this sample visual, but you might also retrieve these values from metadata in production.

import DataViewTableRow = powerbi.DataViewTableRow;

import * as d3 from "d3";
type Selection<T extends d3.BaseType> = d3.Selection<T, any,any, any>;

export interface TestItem {
  Country: string;
  Cost: number;
}

export class Visual implements IVisual {
  private svg: Selection<SVGElement>;
  private g: Selection<SVGElement>;
  private margin = { top: 20, right: 20, bottom: 200, left: 70 };

  constructor(options: VisualConstructorOptions) {
    // append svg graphics
    this.svg = d3.select(options.element).append('svg');
    this.g = this.svg.append('g');
  }

  public update(options: VisualUpdateOptions) {
    // "this" scope will change in the nested function
    var _this = this;

    _this.svg.attr("width", options.viewport.width);
    _this.svg.attr("height", options.viewport.height);
    var gHeight = options.viewport.height
      - _this.margin.top
      - _this.margin.bottom;
    var gWidth = options.viewport.width
      - _this.margin.right
      - _this.margin.left;
    _this.g.attr("width", gWidth);
    _this.g.attr("height", gHeight);
    _this.g.attr('transform',
      'translate(' + _this.margin.left + ',' + _this.margin.top + ')');

    // convert data format
    var dat =
      Visual.converter(options.dataViews[0].table.rows);

    // setup d3 scale
    //var xScale = d3.scaleOrdinal()
    //  .domain(dat.map(function (d) { return d.Country; }));
    //  .rangeRoundBands([0, gWidth], 0.1);
    var xScale = d3.scaleBand()
      .domain(dat.map(function (d) { return d.Country; }))
      .rangeRound([0, gWidth])
      .padding(0.1);
    var yMax =
      d3.max(dat, function (d) { return d.Cost + 10 });
    var yScale = d3.scaleLinear()
      .domain([0, yMax])
      .range([gHeight, 0]);

    // remove exsisting axis and bar
    _this.svg.selectAll('.axis').remove();
    _this.svg.selectAll('.bar').remove();

    // draw x axis
    var xAxis = d3.axisBottom(xScale);
    _this.g
      .append('g')
      .attr('class', 'x axis')
      .style('fill', 'black') // you can get from metadata
      .attr('transform', 'translate(0,' + (gHeight - 1) + ')')
      .call(xAxis)
      .selectAll('text') // rotate text
      .style('text-anchor', 'end')
      .attr('dx', '-.8em')
      .attr('dy', '-.6em')
      .attr('transform', 'rotate(-90)');

    // draw y axis
    var yAxis = d3.axisLeft(yScale);
    _this.g
      .append('g')
      .attr('class', 'y axis')
      .style('fill', 'black') // you can get from metadata
      .call(yAxis);

    // draw bar
    var shapes = _this.g
      .append('g')
      .selectAll('.bar')
      .data(dat);

    shapes.enter()
      .append('rect')
      .attr('class', 'bar')
      .attr('fill', 'green')
      .attr('stroke', 'black')
      .attr('x', function (d) {
        return xScale(d.Country);
      })
      .attr('width', xScale.bandwidth())
      .attr('y', function (d) {
        return yScale(d.Cost);
      })
      .attr('height', function (d) {
        return gHeight - yScale(d.Cost);
      });

    shapes
      .exit()
      .remove();
  }

  public destroy(): void {
    //TODO: Perform any cleanup tasks here
  }

  // convert data as following
  //
  //[
  //  "Japan",
  //  100
  //],
  //[
  //  "America",
  //  300
  //],
  //...
  //
  // -->
  //
  // [{"Country" : "Japan",   "Cost" : 100},
  //  {"Country" : "America", "Cost" : 300},
  // ...]
  //
  public static converter(rows: DataViewTableRow[]): TestItem[] {
    var resultData: TestItem[] = [];

    for (var i = 0;
      i < rows.length;
      i++) {
      var row = rows[i];
      resultData.push({
        Country: row[0] as string,
        Cost: row[1] as number
      });
    }

    return resultData;
  }
}

When you insert this visual in your report, you can see your visual as follows.

Packaging and Distributions

The programming has done.
Now you write your package information (author name, e-mail, supportUrl, and descriptions) in pbiviz.json and create your distributable package using the following command.
After the command has completed, the package file is created as dist/{your project name}.pbiviz. (This .pbiviz file is the zip file, and you can extract and see the resource in this package.)

pbiviz package

After you have created your package (.pbiviz), you can import this package by selecting […] – [import a custom visual] (see the following screenshot) in the visual pane of Power BI Desktop or Power BI services.

 

With Real-time, Embedded, and more …

You can use your custom visuals in Power BI Embedded. That is, you can include your own custom visuals into your PowerBI integrated applications. (But, note that the PowerPoint export is not supported in custom visuals …)

This custom visual can also be updated by real-time in your Power BI dashboard. (For example, pin to the dashboard, and push the rows into the dataset using rest api.)

 

If you have completed, you can share your visuals in the Power BI community (see below), and everybody can download your custom visuals. You can also check these useful visuals as your development examples.

Power BI : Visual Gallery
https://app.powerbi.com/visuals/

Please refer the following useful document for details.

PowerBI-visuals : Developer’s documents
https://github.com/Microsoft/PowerBI-visuals#developing-your-first-powerbi-visual

 

Update history :

03/27/2020  Updated all source code for the latest Power BI Visual Tools (pbiviz) version 3.1 and its template (Also use d3 version 5.x)

 

Categories: Uncategorized

Tagged as: PowerBI