Adding a Second Measure to Custom Visual

[cogsie]

I am new to programming and not even really sure how to ask this question.  Please let me know if anyhting is unclear or you need more information, but here it goes:

I am creating my first custom visual in Power BI.  I want to have overlapping bar charts.  I found an example/tutorial online showing how to create a simple bar chart.  I was able to modify the visual to get the two overlapping bar charts, and the custom visual is looking exactly how I want it (see below).

The problem is that the example I modified restricted the visual to only using one measure (that is why in the screenshot the thin yellow and fat green bars are exactly the same height - they are both based on the same set of values).  I was able to change the capabilities file to allow for 2 measures.  However, I can't figure out how to make it so the second set of bars (yellow bars) uses the second measure.  Can someone help point me in the right direction?

Below is the visual.ts file.  Please let me know if you need any additional information or if anything is unclear.

import "./../style/visual.less";

import powerbi from "powerbi-visuals-api";
import VisualConstructorOptions = powerbi.extensibility.visual.VisualConstructorOptions;
import VisualUpdateOptions = powerbi.extensibility.visual.VisualUpdateOptions;
import IVisual = powerbi.extensibility.visual.IVisual;

import DataView = powerbi.DataView;
import DataViewValueColumn = powerbi.DataViewValueColumn;
import DataViewCategorical = powerbi.DataViewCategorical;
import DataViewCategoricalColumn = powerbi.DataViewCategoricalColumn;
import DataViewCategoryColumn = powerbi.DataViewCategoryColumn;
import PrimitiveValue = powerbi.PrimitiveValue;
import IVisualHost = powerbi.extensibility.visual.IVisualHost;

import IColorPalette = powerbi.extensibility.IColorPalette;
import VisualObjectInstance = powerbi.VisualObjectInstance;
import VisualObjectInstanceEnumeration = powerbi.VisualObjectInstanceEnumeration;
import VisualObjectInstanceEnumerationObject = powerbi.VisualObjectInstanceEnumerationObject;
import EnumerateVisualObjectInstancesOptions = powerbi.EnumerateVisualObjectInstancesOptions;
import Fill = powerbi.Fill;
import VisualTooltipDataItem = powerbi.extensibility.VisualTooltipDataItem;
import ISelectionManager = powerbi.extensibility.ISelectionManager;

import { valueFormatter as vf, textMeasurementService as tms } from "powerbi-visuals-utils-formattingutils";
import IValueFormatter = vf.IValueFormatter;

import { VisualSettings,BarchartProperties } from "./settings";

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

export interface BarchartDataPoint{
    Category: string;
    Value: number;
}

export interface BarchartViewModel{
    IsNotValid: boolean;
    DataPoints?:BarchartDataPoint[];
    Format?: string,
    SortBySize?: boolean;
    XAxisFontSize?: number;
    YAxisFontSize?: number;
    BarColor?: string
    ColumnName?: string;
    MeasureName?: string;
}

export class Barchart implements IVisual {
    private svg: Selection<SVGElement>;
    private barContainer: Selection<SVGGElement>;
    private plotBackground: Selection<SVGRectElement>;
    private barSelection: DataSelection<BarchartDataPoint>;
    private xAxisContainer: Selection<SVGGElement>;
    private yAxisContainer: Selection<SVGGElement>;

    private barContainer2: Selection<SVGGElement>;
    private plotBackground2: Selection<SVGRectElement>;
    private barSelection2: DataSelection<BarchartDataPoint>;
    private xAxisContainer2: Selection<SVGGElement>;
    private yAxisContainer2: Selection<SVGGElement>;


    private hostService: IVisualHost;

    private settings: VisualSettings;

    private viewModel: BarchartViewModel;

    private static margin = {
        top:20,
        right: 20,
        bottom: 20,
        left: 50,
    };




    constructor(options: VisualConstructorOptions) {
        console.log('Constructor executing', options);

        this.hostService = options.host;

        this.svg = d3.select(options.element)
            .append('svg')
            .classed('Barchart',true);
        
        this.barContainer = this.svg
            .append('g')
            .classed('barContainer', true);
        
        this.plotBackground = this.barContainer
            .append('rect')
            .classed('plotBackground', true);
            
        this.xAxisContainer = this.svg
            .append('g')
            .classed('xAxis', true);

        this.yAxisContainer = this.svg
            .append('g')
            .classed('yAxis', true);

        this.barContainer2 = this.svg
            .append('g')
            .classed('barContainer2', true);

        this.plotBackground2 = this.barContainer2
            .append('rect')
            .classed('plotBackground2', true);

        this.xAxisContainer2 = this.svg
            .append('g')
            .classed('xAxis2', true);

        this.yAxisContainer2 = this.svg
            .append('g')
            .classed('yAxis2', true);


        this.settings = VisualSettings.getDefault() as VisualSettings;

    }

    public update(options: VisualUpdateOptions) {

        var viewModel: BarchartViewModel = this.createViewModel(options.dataViews[0]);
        if (viewModel.IsNotValid){
            return;
        }

        //set height and width of root SVG element using viewport passed by Power BI host
        this.svg.attr("height",options.viewport.height);
        this.svg.attr("width", options.viewport.width);

        let marginLeft = Barchart.margin.left * (viewModel.YAxisFontSize / 10);
        let marginBottom = Barchart.margin.bottom * (viewModel.XAxisFontSize / 10);
        let marginTop = Barchart.margin.top;
        let marginRight = Barchart.margin.right;

        let plotArea = {
            x: marginLeft,
            y:marginTop,
            width: (options.viewport.width - (marginLeft + Barchart.margin.right))/2,
            height: (options.viewport.height - (marginTop + marginBottom)),
        };

        let plotArea2 = {
            x: plotArea.x + plotArea.width + 0.3,
            y: marginTop,
        };

        this.barContainer
            .attr("transform","translate(" + plotArea.x + "," + plotArea.y + ")")
            .attr("width",options.viewport.width)
            .attr("height", options.viewport.height);
        
        
        this.plotBackground
            .attr("width", plotArea.width)
            .attr("height", plotArea.height)
            .style("fill","blue");

        this.barContainer2
            .attr("transform", "translate(" + plotArea.x + "," + plotArea.y + ")")
            .attr("width", options.viewport.width)
            .attr("height", options.viewport.height);

        this.plotBackground2
            .attr("width", plotArea.width)
            .attr("height", plotArea.height)
            .style("fill", "none");


        var xScale = d3.scaleBand()
            .rangeRound([0, plotArea.width])
            .padding(0.1)
            .domain(viewModel.DataPoints.map((dataPoint:BarchartDataPoint) => dataPoint.Category));

        this.xAxisContainer
            .attr("class", "xAxis")
            .attr("transform","translate(" + plotArea.x + "," + (plotArea.height + plotArea.y)+")")
            .call(d3.axisBottom(xScale));

        this.xAxisContainer2
            .attr("class", "xAxis2")
            .attr("transform", "translate(" + plotArea.x + "," + (plotArea.height + plotArea.y) + ")")
            .call(d3.axisBottom(xScale));


        d3.select(".xAxis").selectAll("text").style("font-size",viewModel.XAxisFontSize);

        d3.select(".xAxis2").selectAll("text").style("font-size", viewModel.XAxisFontSize);

        let maxValueY: number = d3.max(viewModel.DataPoints,(dataPoint:BarchartDataPoint) => +(dataPoint.Value));

        var valueFormatter = vf.create({
            format: viewModel.Format,
            value: maxValueY/100,
            cultureSelector: this.hostService.locale
        });

        var yScale = d3.scaleLinear()
            .rangeRound([plotArea.height,0])
            .domain([0,maxValueY * 1.02]);

            
        var yAxis = d3.axisLeft(yScale)
            .tickFormat((d) => valueFormatter.format(d));
        //        .tickPadding(12).ticks(5);


        this.yAxisContainer
            .attr("class","yAxis")
            .attr("transform", "translate(" + plotArea.x + "," + plotArea.y + ")")
            .call(yAxis);
/*
        this.yAxisContainer2
            .attr("class", "yAxis2")
            .attr("transform", "translate(" + plotArea2.x + "," + plotArea.y + ")")
            .call(yAxis);
*/

        d3.select(".yAxis").selectAll("text").style("font-size",viewModel.YAxisFontSize);
//        d3.select(".yAxis2").selectAll("text").style("font-size", viewModel.YAxisFontSize);

        this.barSelection2 = this.barContainer2
            .selectAll('.bar')
            .data(viewModel.DataPoints);


        this.barSelection = this.barContainer
            .selectAll('.bar')
            .data(viewModel.DataPoints);

    
        
        const barSelectionMerged = this.barSelection
            .enter()
            .append('rect')
            .merge(<any>this.barSelection)
            .classed('bar',true);

        const barSelectionMerged2 = this.barSelection2
            .enter()
            .append('rect')
            .merge(<any>this.barSelection2)
            .classed('bar', true);


        barSelectionMerged
            .attr("x", (dataPoint: BarchartDataPoint) => xScale(dataPoint.Category))
            .attr("y", (dataPoint: BarchartDataPoint) => yScale(Number(dataPoint.Value)))
            .attr("width", xScale.bandwidth())
            .attr("height", (dataPoint: BarchartDataPoint) => (plotArea.height - yScale(Number(dataPoint.Value))))
            .style("fill",(dataPoint:BarchartDataPoint) => viewModel.BarColor);

        barSelectionMerged2
            .attr("x", (dataPoint: BarchartDataPoint) => xScale(dataPoint.Category) + xScale.bandwidth() / 4)
            .attr("y", (dataPoint: BarchartDataPoint) => yScale(Number(dataPoint.Value)))
            .attr("width", xScale.bandwidth() / 2)
            .attr("height", (dataPoint: BarchartDataPoint) => (plotArea.height - yScale(Number(dataPoint.Value))))
            .style("fill", (dataPoint: BarchartDataPoint) => 'yellow')
            .style("fill-opacity", (dataPoint: BarchartDataPoint) => 1);


        this.barSelection
            .exit()
            .remove();

    }

    public createViewModel(dataView: DataView): BarchartViewModel{

        //handle case where categorical DataView is not valid
        if(typeof dataView === "undefined" ||
            typeof dataView.categorical === "undefined" ||
            typeof dataView.categorical.categories === "undefined" ||
            typeof dataView.categorical.values === "undefined"){
            return {IsNotValid: true};
        }

        this.settings=VisualSettings.parse(dataView) as VisualSettings;

        var categoricalDataView: DataViewCategorical = dataView.categorical;
        var categoryColumn: DataViewCategoricalColumn = categoricalDataView.categories[0];
        var categoryNames: PrimitiveValue[] = categoricalDataView.categories[0].values;
        var categoryValues: PrimitiveValue[] = categoricalDataView.values[0].values;

        var BarchartDataPoints: BarchartDataPoint[] = [];

        for(var i=0; i < categoryValues.length; i++){
            //get category name and category value
            var category : string = <string>categoryNames[i];
            var categoryValue: number = <number>categoryValues[i];
            //add new data point to barchartDataPoints collection
            BarchartDataPoints.push({
                Category: category,
                Value: categoryValue
            });
        }

        //get formatting code for the field that is the measure
        var format: string = categoricalDataView.values[0].source.format

        //get persistent property values
        var SortBySize: boolean = this.settings.barchartProperties.sortBySize;
        var xAxisFontSize: number = this.settings.barchartProperties.xAxisFontSize;
        var yAxisFontSize: number = this.settings.barchartProperties.yAxisFontSize;
        var barColor: string = typeof (this.settings.barchartProperties.barColor) == "string"?
            this.settings.barchartProperties.barColor:
            this.settings.barchartProperties.barColor.solid.color;
        
        //sort dataset rows by measure value instead of cateogry value
        if(SortBySize){
            BarchartDataPoints.sort((x,y) =>{return y.Value - x.Value})
        }

        //return view model to upate method
        return{
            IsNotValid: false,
            DataPoints: BarchartDataPoints,
            Format: format,
            SortBySize: SortBySize,
            BarColor: barColor,
            XAxisFontSize: xAxisFontSize,
            YAxisFontSize: yAxisFontSize,
            ColumnName: dataView.metadata.columns[1].displayName,
            MeasureName:dataView.metadata.columns[0].displayName
        };

    }

    public enumerateObjectInstances(options: EnumerateVisualObjectInstancesOptions): VisualObjectInstanceEnumeration {

        var visualObjects: VisualObjectInstanceEnumerationObject = <VisualObjectInstanceEnumerationObject>VisualSettings.enumerateObjectInstances(this.settings, options);
        
        visualObjects.instances[0].validValues = {
            xAxisFontSize:{numberRange:{min: 10, max:36}},
            yAxisFontSize: { numberRange: { min: 10, max: 36 } },
        };

        return visualObjects
    }

}

[dm-p]

Hi @cogsie,

I can help you with this one - can you please attach your capabilities.json as well? This will specify how the second measure is getting added to the data view and I can make sure I provide you with the correct code.

Thanks,

Daniel

[cogsie]

Thanks so much.  I was doing some more research on this and I think it has something to do with using for...in instead of bind...to.

Below is the capabilities JSON

{
    "dataRoles": [
        {
            "displayName": "Bar Grouping",
            "name": "myCategory",
            "kind": "Grouping"
        },
        {
            "displayName": "Bar Measurement",
            "name": "myMeasure",
            "kind": "Measure"
        }
    ],
    "dataViewMappings": [
        {
            "conditions": [
                {
                    "myCategory": {
                        "max": 1
                    },
                    "myMeasure": {
                        "max": 2
                    }
                }
            ],
            "categorical": {
                "categories": {
                    "for": {
                        "in": "myCategory"
                    },
                    "dataReductionAlgorithm": {
                        "top": {}
                    }
                },
                "values": {
                    "select": [
                        {
                            "bind": {
                                "to": "myMeasure"
                            }
                        }
                    ]
                }
            }
        }
    ],
    "objects": {
        "barchartProperties": {
            "displayName": "Barchart Properties",
            "properties": {
                "sortBySize": {
                    "displayName": "Sort by Size",
                    "type": {
                        "bool": true
                    }
                },
                "barColor": {
                    "displayName": "Bar Color",
                    "type": {
                        "fill": {
                            "solid": {
                                "color": true
                            }
                        }
                    }
                },
                "xAxisFontSize": {
                    "displayName": "X Axis Font Size",
                    "type": {
                        "integer": true
                    }
                },
                "yAxisFontSize": {
                    "displayName": "Y Axis Font Size",
                    "type": {
                        "integer": true
                    }
                }
            }
        }
    }
}

[dm-p]

Hi @cogsie - just getting your code set up and I'm getting some compilation errors due to my settings.ts not being in sync with the code in your visual.ts. Are you able to provide that too (sorry, I should have thought to ask for it orginally)?

One other question - is your visual intending to only display a maximum of two measures, and is the second measure performing a specific role (e.g. as a target vs the other measure)? This is important as it'll have an effect on how we build your view model and capabilties.json to suit.

Thanks,

Daniel

[cogsie]

I really appreciate your help.

Below is the settings.ts file.

My goal with this chart is to compare to values across one or more categories.  For example comparing budgeted amounts to actual amounts for each sales region where the green bars are the budget and the yellow bars are the actuals.  So to answer your question more directly, each bar would be the sume of different measures (e.g. actual, budget).  I hope that makes sense.

"use strict";

import { dataViewObjectsParser } from "powerbi-visuals-utils-dataviewutils";
import DataViewObjectsParser = dataViewObjectsParser.DataViewObjectsParser;
import powerbi from "powerbi-visuals-api";
import Fill = powerbi.Fill;

export class VisualSettings extends DataViewObjectsParser {
  // public dataPoint: dataPointSettings = new dataPointSettings();
  public barchartProperties: BarchartProperties = new BarchartProperties();
}

export class BarchartProperties {
  sortBySize: boolean = true;
  xAxisFontSize: number = 10;
  yAxisFontSize: number = 10;
  barColor: Fill = { "solid": { "color": "#018a80" } }; // default color is  teal
}

[dm-p]

Hi @cogsie.,

Thanks very much - I can get the code to compile now.

Looks like you're trying something like a bullet chart?

If so, I'd suggest creating specific data roles for each measure as they each have an intended purpose. This will make your code easier to manage rather than using a single data role with multiple measures and more intuitive to the end-user. Using the same data role with multiple measures will cause a challenge in that they will all have the same role type, processing the data view and assigning the measures to the appropriate bar will depend on the order they're added by the end user.

I'll proceed on the basis that we want two distinct measure roles - for the sake of illustration, I'll call them actual and budget and provide you with the changes required to make that work. Does that sounds like a good approach to you? If you want to stick with the existing approach, let me know and I'll provide you with a solution as per your requirements.

Thanks,

Daniel

[cogsie]

This sounds like a great approach.  And you are correct, it is similar to the bullet chart, but much simpler.

[dm-p · Accepted solution]

Great!

I've added all files to this gist for you to download the full code, as I'd like to focus on explaining the changes I made so that you can build on them accordingly.

capabilities.json - dataRoles

I've modified the dataRoles to the following:

    "dataRoles": [
        {
            "displayName": "Bar Grouping",
            "name": "myCategory",
            "kind": "Grouping"
        },
        {
            "displayName": "Actual",
            "name": "actual",
            "kind": "Measure"
        },
        {
            "displayName": "Budget",
            "name": "budget",
            "kind": "Measure"
        }
    ]

 This will modify the visual's data roles as follows:

Note that your development visual will likely still have the now removed myMeasure role in there, so your best bet is to ensure everything is fully removed and re-applied when working with the new code I've provided.

capabilities.json - dataViewMappings

The dataViewMappings now look as follows:

    "dataViewMappings": [
        {
            "conditions": [
                {
                    "myCategory": {
                        "max": 1
                    },
                    "actual": {
                        "max": 1
                    },
                    "budget": {
                        "max": 1
                    }
                }
            ],
            "categorical": {
                "categories": {
                    "for": {
                        "in": "myCategory"
                    },
                    "dataReductionAlgorithm": {
                        "top": {}
                    }
                },
                "values": {
                    "select": [
                        {
                            "bind": {
                                "to": "actual"
                            }
                        },
                        {
                            "bind": {
                                "to": "budget"
                            }
                        }
                    ]
                }
            }
        }
    ]

This will group by myCategory and then bind both measures to each. For my test data, the visual table looks as follows:

Note that this is a public dataset and I'm treating Tip as actual and Total Bill as budget.

Let's take a look at the dataViewMapping in the developer visual for the data I've added:

The values array now has two entries - one for actual and one for budget. We can see this if we expand the source object for each and then roles - and this is how you can determine which entry is performing which role. The measure values for each are then in the associated values array (which will match the order of myCategory). For brevity, I've only expanded this for actual.

visual.ts - BarchartDataPoint interface

We need to change the interface spec to ensure that the 'shape' of each data point is correct. This now looks as follows:

export interface BarchartDataPoint{
    category: string;
    actual: number;
    budget: number;
}

(it's not essential, but good practice to use camelCase for properties, so I have modified all to suit)

This now means that our view model expects two numeric values for each category, which matches our dataViewMappings, so we need to map them into the view model next.

visual.ts - createViewModel function

If you're making the above changes incrementally, you'll now see errors in your code, because the BarchartDataPoint interface 'shape' does not match the object you're assigning in the createViewModel function. We need to update this.

With the above in mind, and how the dataViewMapping looks, we need to change the way we iterate over it.

• You were previously iterating over the measure values; I have modified this to iterate over the category values just to make the next bit easier.
• I'm going to do the simplest possible solution for the measures using array accessors of [0] for actual and [1] for budget.
• To make your code super-resilient, you would filter the values array by role to ensure the value is definitely provided, but we can worry about that waaaaay later 😉

The portion of the function code to map the view model now looks as follows:

    categoryNames.map((c, ci) => { /** c= category, ci = category array index */
        BarchartDataPoints.push({
            category: <string>c,
            actual: <number>categoricalDataView.values[0].values[ci],
            budget: <number>categoricalDataView.values[1].values[ci]
        });
    });

You have a sort lower down, so that's been modified to use the actual value:

    if(SortBySize){
        BarchartDataPoints.sort((x,y) =>{return y.actual - x.actual})
    }

visual.ts - update function

It's now a case or binding everything from the view model correctly to your chart logic.

The first fix is your maxValueY assignment - we just get this to match the highest value now that you have two measures:

    let maxValueY: number = d3.max(
        viewModel.DataPoints,
        (dataPoint:BarchartDataPoint) => 
            /** Get the higher of either measure per group */
            +Math.max(dataPoint.actual, dataPoint.budget) 
    );

Now, the actual shape! barSelectionMerged gets updated to now use the actual property from the data point:

    barSelectionMerged
        .attr("x", (dataPoint: BarchartDataPoint) => xScale(dataPoint.category))
        .attr("y", (dataPoint: BarchartDataPoint) => yScale(Number(dataPoint.actual)))
        .attr("width", xScale.bandwidth())
        .attr("height", (dataPoint: BarchartDataPoint) => (plotArea.height - yScale(Number(dataPoint.actual))))
        .style("fill",(dataPoint:BarchartDataPoint) => viewModel.BarColor);

...and barSelectionMerged2 gets the budget property as part of its yScale:

    barSelectionMerged2
        .attr("x", (dataPoint: BarchartDataPoint) => xScale(dataPoint.category) + xScale.bandwidth() / 4)
        .attr("y", (dataPoint: BarchartDataPoint) => yScale(Number(dataPoint.budget)))
        .attr("width", xScale.bandwidth() / 2)
        .attr("height", (dataPoint: BarchartDataPoint) => (plotArea.height - yScale(Number(dataPoint.budget))))
        .style("fill", (dataPoint: BarchartDataPoint) => 'yellow')
        .style("fill-opacity", (dataPoint: BarchartDataPoint) => 1);

Verifying

We can now test in the developer visual! Here's how it looks for my data above:

And hopefully, this is where you need to be 🙂

Good luck!

Daniel

---

If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂

[cogsie]

Wow, this is incredible.  This is exactly what I need.  And thank you for your detailed explanation.  I have learned a tremendous amount from all this.  Thanks.

[dm-p]

You're very welcome! Glad you found it useful 🙂