Creating a Custom PowerBI Visualization from Scratch with Sachin Patney

Sign in to queue

Description

About a week or so ago Nico and Sachin announced a really cool visualization contest where people could win actual money! They were eager for everyone to be successful so they wanted to have an end-to-end demonstration on how one would go about building a visualization from scratch. In this video Sachin does just that: he takes us step by step through the process of building a really cool visualization directly in PowerBI. The whole process was super intuitive and showed the power of the engine running PowerBI. Enjoy!

Embed

Download

Download this episode

The Discussion

  • User profile image
    Henrik Zacher Molbech

    Great demo. Could you post the code?

  • User profile image
    Tony

    Hi, am having trouble with some of your code. Are you showcasing features that are not currently available to the public?

    For example, this does not work for me:
    Line 74: private selectionManager: utility.SelectionManager;
    Line 98: var id = SelectionIdBuilder
    Line 118: this.selectionManager = new utility.SelectionManager({ hostServices: options.host });


    Seems as though the "utility" method is not available to me??

    Thanks,
    Tony

  • User profile image
    Tony

    BTW I translated your code. Hopefully there are no typos.

    module powerbi.visuals {
    export interface StreamData {
    dataPoints: StreamDataPoint[][];
    legendData: LegendData;
    }

    export interface StreamDataPoint {
    x: number;
    y: number;
    y0?: number;
    identity: SelectionId;
    }

    export class StreamGraph implements IVisual {
    public static capabilities: VisualCapabilities = {
    dataRoles: [
    {
    name: 'Category',
    kind: VisualDataRoleKind.Grouping,
    displayName: 'Category',
    }, {
    name: 'Series',
    kind: VisualDataRoleKind.Grouping,
    displayName: 'Series',
    }, {
    name: 'Y',
    kind: VisualDataRoleKind.Measure,
    displayName: data.createDisplayNameGetter('Role_DisplayName_Values'),
    },
    ],
    dataViewMappings: [{
    conditions: [
    { 'Category': { max: 1 }, 'Series': { max: 0 } },
    { 'Category': { max: 1 }, 'Series': { min: 1, max: 1 }, 'Y': { max: 1 } }
    ],
    categorical: {
    categories: {
    for: { in: 'Category' },
    dataReductionAlgorithm: { top: {} }
    },
    values: {
    group: {
    by: 'Series',
    select: [{ for: { in: 'Y' } }],
    dataReductionAlgorithm: { top: {} }
    }
    },
    }
    }],
    objects: {
    general: {
    displayName: 'General',
    properties: {
    wiggle: {
    type: {bool: true },
    displayName: 'Wiggle'
    }
    }
    }
    },
    drilldown:{roles:['Series']}
    };

    private static VisualClassName = 'streamGraph';
    private static Layer: ClassAndSelector = {
    class: 'layer',
    selector: '.layer'
    };

    private element: JQuery;
    private svg: D3.Selection;
    private axis: D3.Selection;
    private colors: IDataColorPalette;
    private selectionManager: utility.SelectionManager;
    private dataView: DataView;
    private legend: ILegend

    public static converter(dataView: DataView, colors: IDataColorPalette): StreamData {
    var catDV: DataViewCategorical = dataView.categorical;
    var cat = catDV.categories[0];
    var catValues = cat.values;
    var values = catDV.values;
    var dataPoints: StreamDataPoint[][] = [];
    var legendData: LegendData = {
    dataPoints: [],
    title: values[0].source.displayName
    };
    for (var i = 0, iLen = values.length; i < iLen; i++) {
    dataPoints.push([]);
    legendData.dataPoints.push({
    label: values[i].source.groupName,
    color: colors.getColorByIndex(i).value,
    icon: LegendIcon.Box,
    selected: false,
    identity: null
    });
    for (var k = 0, klen = values[i].values.length; k < klen; k++) {
    var id = SelectionIdBuilder
    .builder()
    .widthSeries(dataView.categorical.values, dataView.categorical.values[i])
    .createSelectionId();
    dataPoints[i].push({
    x: k,
    y: values[i].values[k],
    identity: id
    });
    }
    }

    return {
    dataPoints: dataPoints,
    legendData: legendData
    };
    }

    public init(options: VisualInitOptions): void {
    var element = options.element;
    this.selectionManager = new utility.SelectionManager({ hostServices: options.host });
    var svg = this.svg = d3.select(element.get(0))
    .append('svg')
    .classed(StreamGraph.VisualClassName, true);

    this.axis = this.svg.append("g");

    this.colors = options.style.colorPalette.dataColors;

    this.legend = createLegend(element, false, null);
    }

    public update(options: VisualUpdateOptions) {
    if (!options.dataViews || !options.dataViews[0]) return;
    var duration = options.suppressAnimations ? 0 : AnimatorCommon.MinervaAnimationDuration;

    var dataView = this.dataView = options.dataViews[0];
    var data = StreamGraph.converter(dataView, this.colors);
    var dataPoints = data.dataPoints;
    var viewport = options.viewport;
    var margins: IMargin = { left: 20, right: 20, bottom: 25, top: this.legend.getMargins().height };

    this.legend.drawLegend(data.legendData, viewport);

    var height = options.viewport.height - margins.top;

    this.svg.attr({
    'width': viewport.width,
    'height': height
    });

    var stack = d3.layout.stack();

    if (this.getWiggle(dataView)) { stack.offset('wiggle') }
    var layers = stack(dataPoints)

    var x = d3.scale.linear()
    .domain([0, dataPoints[0].length - 1])
    .range([margins.left, viewport.width - margins.right]);

    var y = d3.scale.linear()
    .domain([0, d3.max(layers,(layer) => {
    return d3.max(layer,(d) => {
    return d.y0 + d.y;
    });
    })]).range([height - margins.bottom, margins.top]);

    var area = d3.svg.area()
    .interpolate('basis')
    .x(d => x(d.x))
    .y0(d => y(d.y0))
    .y1(d => y(d.y0 + d.y));

    var sm = this.selectionManager;
    var selection = this.svg.selectAll(StreamGraph.Layer.selector)
    .data(layers);

    selection.enter()
    .append('path')
    .on('click', function (d) {
    sm.select(d[0].identity).then(ids => {
    if (ids.length > 0) {
    selection.style('opacity', 0.5);
    d3.select(this).style('opactiy', 1);
    } else {
    selection.style('opacity', 1);
    }
    })

    }).classed(StreamGraph.Layer.class, true)

    selection
    .style("fill",(d, i) => this.colors.getColorByIndex(i).value)
    .transition()
    .duration(duration)
    .attr("d", area);

    selection.exit().remove();

    this.drawAxis(viewport, margins);
    }

    private drawAxis(viewport: IViewport, margins: IMargin) {
    var dataView = this.dataView;
    var xS = d3.time.scale()
    var values = dataView.categorical.categories[0].values;
    xS.domain([values[0], values[values.length - 1]])
    .range([margins.left , viewport.width - margins.right]);

    var xAxis = d3.svg.axis().scale(xS).ticks(5);

    this.axis.attr("class", " x axis")
    .attr("transform", "translate(0," + (viewport.height - margins.bottom - margins.top) + ")" )
    .call(xAxis);
    }

    private getWiggle(dataView: DataView) {
    if (dataView) {
    var objects = dataView.metadata.objects;
    if (objects) {
    var general = objects['general'];
    if (general) {
    return <boolean>general['wiggle'];
    }
    }
    }

    return true;
    }

    public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstance {
    var instances: VisualObjectInstance[] = [];
    var dataView = this.dataView
    switch (options.objectName) {
    case 'general':
    var general: VisualObjectInstance = {
    objectName: 'general',
    displayName: 'General',
    selector: null,
    properties: {
    wiggle: this.getWiggle(this.dataView)
    }
    };
    instances.push(general);
    break;
    }

    return instances;
    }
    }
    }

  • User profile image
    Sachinio

    Henrik, here is the pull request with the code. https://github.com/Microsoft/PowerBI-visuals/pull/99. Tony, yes, the selection manager isn't up to date in the public service yet, but you should be able to use it in Github project. This change will make it to the service in a week. Hope this helps.

  • User profile image
    skaratela

    Hi Tony!

    Great work on typing out the code - there was only one small typo which I have corrected:

    from: 

    .widthSeries(dataView.categorical.values, dataView.categorical.values[i])

    to: 

    .withSeries(dataView.categorical.values, dataView.categorical.values[i])

    And here is the full corrected code:

    module powerbi.visuals {
        export interface StreamData {
            dataPoints: StreamDataPoint[][];
            legendData: LegendData;
        }
    
        export interface StreamDataPoint {
            x: number;
            y: number;
            y0?: number;
            identity: SelectionId;
        }
    
        export class StreamGraph implements IVisual {
            public static capabilities: VisualCapabilities = {
                dataRoles: [
                    {
                        name: 'Category',
                        kind: VisualDataRoleKind.Grouping,
                        displayName: 'Category',
                    }, {
                        name: 'Series',
                        kind: VisualDataRoleKind.Grouping,
                        displayName: 'Series',
                    }, {
                        name: 'Y',
                        kind: VisualDataRoleKind.Measure,
                        displayName: data.createDisplayNameGetter('Role_DisplayName_Values'),
                    },
                ],
                dataViewMappings: [{
                    conditions: [
                        { 'Category': { max: 1 }, 'Series': { max: 0 } },
                        { 'Category': { max: 1 }, 'Series': { min: 1, max: 1 }, 'Y': { max: 1 } }
                    ],
                    categorical: {
                        categories: {
                            for: { in: 'Category' },
                            dataReductionAlgorithm: { top: {} }
                        },
                        values: {
                            group: {
                                by: 'Series',
                                select: [{ for: { in: 'Y' } }],
                                dataReductionAlgorithm: { top: {} }
                            }
                        },
                    }
                }],
                objects: {
                    general: {
                        displayName: 'General',
                        properties: {
                            wiggle: {
                                type: { bool: true },
                                displayName: 'Wiggle'
                            }
                        }
                    }
                },
                drilldown: { roles: ['Series'] }
            };
    
            private static VisualClassName = 'streamGraph';
            private static Layer: ClassAndSelector = {
                class: 'layer',
                selector: '.layer'
            };
    
            private element: JQuery;
            private svg: D3.Selection;
            private axis: D3.Selection;
            private colors: IDataColorPalette;
            private selectionManager: utility.SelectionManager;
            private dataView: DataView;
            private legend: ILegend
    
            public static converter(dataView: DataView, colors: IDataColorPalette): StreamData {
                var catDV: DataViewCategorical = dataView.categorical;
                var cat = catDV.categories[0];
                var catValues = cat.values;
                var values = catDV.values;
                var dataPoints: StreamDataPoint[][] = [];
                var legendData: LegendData = {
                    dataPoints: [],
                    title: values[0].source.displayName
                };
                for (var i = 0, iLen = values.length; i < iLen; i++) {
                    dataPoints.push([]);
                    legendData.dataPoints.push({
                        label: values[i].source.groupName,
                        color: colors.getColorByIndex(i).value,
                        icon: LegendIcon.Box,
                        selected: false,
                        identity: null
                    });
                    for (var k = 0, klen = values[i].values.length; k < klen; k++) {
                        var id = SelectionIdBuilder
                            .builder()
                            .withSeries(dataView.categorical.values, dataView.categorical.values[i])
                            .createSelectionId();
                        dataPoints[i].push({
                            x: k,
                            y: values[i].values[k],
                            identity: id
                        });
                    }
                }
    
                return {
                    dataPoints: dataPoints,
                    legendData: legendData
                };
            }
    
            public init(options: VisualInitOptions): void {
                var element = options.element;
                this.selectionManager = new utility.SelectionManager({ hostServices: options.host });
                var svg = this.svg = d3.select(element.get(0))
                    .append('svg')
                    .classed(StreamGraph.VisualClassName, true);
    
                this.axis = this.svg.append("g");
    
                this.colors = options.style.colorPalette.dataColors;
    
                this.legend = createLegend(element, false, null);
            }
    
            public update(options: VisualUpdateOptions) {
                if (!options.dataViews || !options.dataViews[0]) return;
                var duration = options.suppressAnimations ? 0 : AnimatorCommon.MinervaAnimationDuration;
    
                var dataView = this.dataView = options.dataViews[0];
                var data = StreamGraph.converter(dataView, this.colors);
                var dataPoints = data.dataPoints;
                var viewport = options.viewport;
                var margins: IMargin = { left: 20, right: 20, bottom: 25, top: this.legend.getMargins().height };
    
                this.legend.drawLegend(data.legendData, viewport);
    
                var height = options.viewport.height - margins.top;
    
                this.svg.attr({
                    'width': viewport.width,
                    'height': height
                });
    
                var stack = d3.layout.stack();
    
                if (this.getWiggle(dataView)) { stack.offset('wiggle') }
                var layers = stack(dataPoints)
    
                var x = d3.scale.linear()
                    .domain([0, dataPoints[0].length - 1])
                    .range([margins.left, viewport.width - margins.right]);
    
                var y = d3.scale.linear()
                    .domain([0, d3.max(layers, (layer) => {
                        return d3.max(layer, (d) => {
                            return d.y0 + d.y;
                        });
                    })]).range([height - margins.bottom, margins.top]);
    
                var area = d3.svg.area()
                    .interpolate('basis')
                    .x(d => x(d.x))
                    .y0(d => y(d.y0))
                    .y1(d => y(d.y0 + d.y));
    
                var sm = this.selectionManager;
                var selection = this.svg.selectAll(StreamGraph.Layer.selector)
                    .data(layers);
    
                selection.enter()
                    .append('path')
                    .on('click', function (d) {
                        sm.select(d[0].identity).then(ids => {
                            if (ids.length > 0) {
                                selection.style('opacity', 0.5);
                                d3.select(this).style('opactiy', 1);
                            } else {
                                selection.style('opacity', 1);
                            }
                        })
    
                    }).classed(StreamGraph.Layer.class, true)
    
                selection
                    .style("fill", (d, i) => this.colors.getColorByIndex(i).value)
                    .transition()
                    .duration(duration)
                    .attr("d", area);
    
                selection.exit().remove();
    
                this.drawAxis(viewport, margins);
            }
    
            private drawAxis(viewport: IViewport, margins: IMargin) {
                var dataView = this.dataView;
                var xS = d3.time.scale()
                var values = dataView.categorical.categories[0].values;
                xS.domain([values[0], values[values.length - 1]])
                    .range([margins.left, viewport.width - margins.right]);
    
                var xAxis = d3.svg.axis().scale(xS).ticks(5);
    
                this.axis.attr("class", " x axis")
                    .attr("transform", "translate(0," + (viewport.height - margins.bottom - margins.top) + ")")
                    .call(xAxis);
            }
    
            private getWiggle(dataView: DataView) {
                if (dataView) {
                    var objects = dataView.metadata.objects;
                    if (objects) {
                        var general = objects['general'];
                        if (general) {
                            return <boolean>general['wiggle'];
                        }
                    }
                }
    
                return true;
            }
    
            public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstance {
                var instances: VisualObjectInstance[] = [];
                var dataView = this.dataView
                switch (options.objectName) {
                    case 'general':
                        var general: VisualObjectInstance = {
                            objectName: 'general',
                            displayName: 'General',
                            selector: null,
                            properties: {
                                wiggle: this.getWiggle(this.dataView)
                            }
                        };
                        instances.push(general);
                        break;
                }
    
                return instances;
            }
        }
    }

     

    Regards,

     

    Shaheen K

  • User profile image
    sandeepkumar

    Hi Sachin,

    Could you refer any custom visual for rendering a circle svg on bing map.

    I am using below code, but circle is hided by map,

    public init(options: VisualInitOptions): void

    {
    this.colorPalette = options.style.colorPalette.dataColors;
    // element is the element in which your visual will be hosted.
    this.hostContainer = options.element.css('overflow-x', 'hidden');

    setTimeout(function(){
    map = new Microsoft.Maps.Map(options.element.get(0), {
    credentials: '**************************************************************',
    center: new Microsoft.Maps.Location(52, -115),
    zoom: 4
    });
    });

    var svg = this.svg = d3.select(options.element.get(0)).append('svg');

    Microsoft.Maps.registerModule("D3OverlayModule", D3OverlayManager);

    Microsoft.Maps.loadModule("D3OverlayModule");
    { onloadeddata : this.loadCircle()};

    }

     

    --------------------------------------------------------------
    public loadCircle() {
    d3MapTools = new D3OverlayManager(map);
    stateLayer = d3MapTools.addLayer({
    loaded: function (svg, projection) {
    svg.attr("width", 50).attr("height", 50).append("circle").attr("cx", 25).attr("cy", 25).attr("r", 25).style("fill", "purple");
    }
    });
    }

     

    Thanks

    Sandeep

  • User profile image
    Rafael

    Thanks for the video, really useful.

    Don't mean to hijack the thread, let me know if I should post the question on another place.

    I created a custom visualization similar to the Card but displays only the most recent value.

    I can see the control working as expected at the report level, but when I pin the visual to a dashboard it's empty. I confirmed the "Export Data" option returns the data.

    Any idea why is not showing up?

    Thanks!


    module powerbi.visuals {
    export interface CardStreamData {
    dataPoints: CardStreamDataPoint[];
    };

    export interface CardStreamDataPoint{
    dayDateTime: Date;
    value: number;
    }

    export class ControlTest implements IVisual {

    public static capabilities: VisualCapabilities = {
    dataRoles: [
    {
    name: 'Series',
    kind: VisualDataRoleKind.Grouping,
    displayName: 'Series'
    },{
    name: 'Values',
    kind: VisualDataRoleKind.Measure,
    displayName: 'Value'
    }
    ],
    objects: {
    general: {
    properties: {
    formatString: {
    type: { formatting: { formatString: true } },
    }
    }
    }
    },
    dataViewMappings:[{
    conditions: [
    { 'Series':{min:1, max:1}, 'Values': { max: 1 } }
    ],
    categorical: {
    values: {
    group:{
    by: 'Series',
    select: [{ for: {in: 'Values'} }],
    dataReductionAlgorithm: { bottom : {} }
    }
    }
    }
    }]
    };

    private static VisualClassName = 'controlTest';
    private static Layer: ClassAndSelector = {
    class: 'layer',
    selector : '.layer'
    };

    private element: JQuery;
    private dataView: DataView;

    private value = 0;

    public static converter(dataView: DataView): CardStreamData {
    var dataPoints = [];

    if (dataView && dataView.categorical && dataView.categorical.values)
    {
    var values = dataView.categorical.values;

    for (var i = 0, iLen = values.length; i < iLen; i++){
    var timestamp : Date = new Date(values[i].source.groupName);

    // todo: add property to manage number of decimals
    var sourceValue: number = values[i].values[0].toFixed(2);
    var entry : CardStreamDataPoint = {
    dayDateTime: timestamp,
    value: sourceValue
    }

    dataPoints.push(entry);
    }

    }

    return {
    dataPoints: dataPoints
    }

    //commented so I can return hard-coded data for testing
    /*return {
    dataPoints: [{dayDateTime:new Date(2016, 0, 1, 1, 10, 0), value: 5.6},
    {dayDateTime:new Date(2016, 0, 3, 1, 10, 0), value: 5.2}
    ,{dayDateTime:new Date(2016, 0, 2, 1, 10, 0), value: 5.1}
    ,{dayDateTime:new Date(2016, 0, 6, 1, 11, 0), value: 5.66}
    ,{dayDateTime:new Date(2016, 0, 5, 1, 10, 0), value: 5.5}
    ,{dayDateTime:new Date(2016, 0, 4, 1, 10, 0), value: 5.4}]
    }*/
    }

    private getPropertyByName(dataView: DataView, propertyName: string){
    if (dataView){
    var objects = dataView.metadata.objects;
    if (objects){
    var general = objects['general'];
    if (general) {
    return <string>general[propertyName];
    }
    }
    }
    }

    public init(options: VisualInitOptions): void {
    this.element = options.element;
    var element = options.element;

    // hard coded styling for now...
    // todo: add format, aligment properties
    this.element.text(this.value).css('font-size', '40px').css('font-weight', 'bold');
    }

    public update(options: VisualUpdateOptions) {
    if (!options.dataViews || !options.dataViews[0]) return;

    var dataView = this.dataView = options.dataViews[0];
    var data = ControlTest.converter(dataView);
    var dataPoints = data.dataPoints;
    var viewport = options.viewport;

    // array sort
    var sort_by = function(field, reverse, primer){

    var key = primer ?
    function(x) {return primer(x[field])} :
    function(x) {return x[field]};

    reverse = !reverse ? 1 : -1;

    return function (a, b) {
    return a = key(a), b = key(b), reverse * ((a > b) - (b > a));
    }
    }

    var sortedDataPoints = data.dataPoints.sort(sort_by('dayDateTime', true, function(a){return a}))
    //alert('' +sortedDataPoints[0].dayDateTime +'=>' + sortedDataPoints[0].value);

    // return the first entry of the sorted data points
    this.value = sortedDataPoints[0].value;
    this.element.text(this.value);
    }
    }
    }

  • User profile image
    Happy

    Please explain your code in littler bit details. It very difficult to understand your code.

  • User profile image
    Rupesh Mishra

    Need to implement pagination in with custom Visual. How I can I achieve that

  • User profile image
    mhardy

    This is great. Very helpful. Would love to see a similar demo using Visual Studio and creating a pbiviz.

  • User profile image
    arunpandiia​nn

     I am having a problem , that dataView object having the unique values or rows in the table .

    i have tried, giving the dataRoles of kind: powerbi.VisualDataRoleKind to Grouping,Measure and GroupingorMeasure. I have even tried out giving the dataViewMappings to categorical(dataReductionAlgorithm: { top: {} }) as well as values(select: [{ bind: { to: 'Y' } }]).I have tried by giving Do not summarize option,keep duplicates option, changed the type of the table to whole number ,text,decimal,etc .,but nothing worked for me. what iam missing and what i have to do to bind the entire table as it is in powerbi dev tool.

    Below my code,

    public static capabilities: VisualCapabilities = {
    // This is what will appear in the 'Field Wells' in reports
    dataRoles: [
    {
    displayName: 'Category',
    name: 'Category',
    kind: powerbi.VisualDataRoleKind.Grouping,
    },
    {
    displayName: 'Y Axis',
    name: 'Y',
    kind: powerbi.VisualDataRoleKind.Measure,
    },
    ],
    // This tells power bi how to map your roles above into the dataview you will receive
    dataViewMappings: [{
    categorical: {
    categories: {
    for: { in: 'Category' },
    dataReductionAlgorithm: { top: {} }
    },
    values: {
    select: [{ bind: { to: 'Y' } }]
    },
    }
    }],
    // Objects light up the formatting pane
    objects: {
    general: {
    displayName: data.createDisplayNameGetter('Visual_General'),
    properties: {
    formatString: {
    type: { formatting: { formatString: true } },
    },
    },
    },
    }
    };

    Thanks in advance.

  • User profile image
    arunpandiia​nn

    Hi Sachin,

    I am trying to bind the entire table as it is in  power bi custom visuals, i had tried all known ways , but still powerbi desktop is removing the duplicate rows and get sorted .please give me a solution for this.

    Thanks in Advance.

  • User profile image
    Siddhi Bhingarde

    Hello Sachin,
    I have started with basic code to display text. When I Compile + run i get error message 'Compilation error in yourvisual'.

    Code :
    module powerbi.visuals{
    import ClassAndSelector = jsCommon.CssConstants.ClassAndSelector;
    export class StreamGraph implements IVisual{
    public static capabilities: VisualCapabilities = {};
    private static VisualClassName = 'streamGraph';
    private static Layer : ClassAndSelector = {
    class: 'layer',
    selector: '.layer'
    };

    private element: JQuery;
    private svg: D3.Selection;
    private axix: D3.Selection;
    private colors: IDataColorPalette;
    private selectionManager: utility.SelectionManager;
    private dataview: DataView;
    private legend: ILegend;

    public static converter(dataView: DataView, colors: IDataColorPalette){

    }

    public init(options: VisualInitOptions): void {
    this.element = options.element;
    this.element.text('Have Success').css('font-size','30px');
    }

    public update(options: VisualUpdateOptions){

    }
    }
    }

    Any idea how to resolve it?

    Thanks,
    Siddhi

Add Your 2 Cents