Power BI Custom Visual - Grouping data

[Anonymous]

Hi,

I created a simple clustered bar chart by using this example in the microsoft documentation on how to group values in the data view mapping:

"dataViewMappings": [
    {
        "categorical": {
            "categories": {
                "for": {
                    "in": "category"
                }
            },
            "values": {
                "group": {
                    "by": "series",
                    "select": [{
                            "for": {
                                "in": "measure"
                            }
                        }
                    ]
                }
            }
        }
    }
]

Now I want to map the measure data role so that it is grouped by the data role series, but also by a new (grouping) data role series1. I want to get the data, from the data view, with the following structure: series > series1 > measure.

I need this not for the bar chart itself but to be able to create a multi level legend that will display the series and series1 of the measure.

Tried to modify the data view mapping, but didnt manage to find a working solution.

Could you please give me some feedback on how to achieve this?

Thank you!

[dm-p]

Hi @Anonymous, and welcome aboard 🙂

The challenge with dataViewMappings is that they can only produce a single query against the data model, so sometimes this has to result in some "creative" thinking. Often you will need to think about how you can bring this out so that you can further process it in your viewmodel to match the ideal "shape" for your visual.

It's also possible that categorical might not be the correct choice here as it doesn't group further using this method, but there might be ways to structure the categories object at a higher level so that it meets your requirements. However this will depend entirely on your data and how it needs to be ultimately utilised in the visual.

Is it possible for you to provide a sample of your data, and a mockup of what you would like the legend to look like? If so, I can take a stab at what I might do in thie situation and that might give you some ideas to work off.

Regards,

Daniel

[Anonymous]

Hi Daniel,

Thank you for the feedback.

You are probably right and I should try to use a different data view mapping, instead of trying to do this using categorical (table maybe?).

I was testing this by using the tutorial's data (US_Sales_Analysis) and I was trying to do a simple legend like this, where I could display Item Group but also Demographic (while the chart remains grouped by Item Group only):

But I am failing to understand how to get all the required data to my visual.

I am currently using categorical in the data view mapping:

            "categorical": {
                "categories": {
                    "for": {
                        "in": "category"
                    }
                },
                "values": {
                    "group": {
                        "by": "series",
                        "select": [
                            {
                                "for": {
                                    "in": "measure"
                                }
                            }
                        ]
                    }
                }
            }

And I was pushing the data to the view model using the same logic as in the microsoft tutorial (example 6)

   private getViewModel(options: VisualUpdateOptions): ViewModel{
        //declare the data view
        let dataView = options.dataViews[0];
        let categoricalDataView = dataView.categorical;

        //set an empty view model
        let viewModel: ViewModel = {
            Categories: [],
            minValue: 0,
            maxValue: 0,
            highlights: false
        };

        //return empty view model if there's no data to show
        if (!categoricalDataView  ||
            !categoricalDataView.categories ||
            !categoricalDataView.categories[0].source ||
            !categoricalDataView.values) {
            return viewModel;
        }

        //declare categories, series and values variables
        let categories = categoricalDataView.categories[0];
        let series = categoricalDataView.values.grouped();
        let values = dataView.categorical.values;

        // iterate categories e series and push data to the view
        categories.values.map((category: PrimitiveValue, categoryIndex: number) => {
            viewModel.Categories.push({
                category: category.toString(),
                values: series.map((group: DataViewValueColumnGroup) => {
                    return {
                        series: group.name.toString(),
                        value: <number>group.values[0].values[categoryIndex],
                        color: this.host.colorPalette.getColor(group.name.toString()).value,
                        identity: this.host.createSelectionIdBuilder()
                                    .withCategory(categories, categoryIndex)
                                    .withSeries(values, group)
                                    .createSelectionId(),
                        highlighted: group.values[0].highlights ? group.values[0].highlights[categoryIndex] ? true : false : false
                    }
                })
            });
        });

        //calculate min value
        viewModel.minValue = d3.min(viewModel.Categories, category => d3.min(category.values, d => d.value));
        
        //calculate max value
        viewModel.maxValue = d3.max(viewModel.Categories, category => d3.max(category.values, d => d.value));
        
        //update highlights in viewModel
        for (let i = 0, len = viewModel.Categories.length; i < len; i++) {
            if(viewModel.Categories[i].values.filter(d => d.highlighted).length > 0){
                viewModel.highlights = true;
                break;
            }
        };

        return viewModel;
    }