Custom Visual - Selecting, Filtering and Highlighting

[diaAzul]

I am developing a series of custom visual, one of which is a population pyramid. The code for the visual is on Github:

https://github.com/DiaAzul/powerBI-visual-PopPyramid3/tree/Version-1.10.0

The visual displays two horizontal bar charts either side of a central axis, with or without reference bars and has a several customisable options for text, colour, labels, etc...

I have implemented highlighting, such that when data in another visual is selected it causes the relevant bars in the population pyramid to become highlighted.

I have also implemented selections (with interactivity services and selectionHandler) to select individual/ multiple bars; and also by clicking on the axis labels for age, and gender label titles to select multiple bars at the same time.

My issues are:

1/ Using selection handler it is possible to select and individual bar and then have it cross filter other visuals, however, whilst it is possible to select multiple bars in the population pyramid the cross filtering fails (i.e. the other visuals are grey-boxed). Note, it is possible to select multiple categories (age or gender) and maintain cross filtering. So the problem is within selection handler/ interactivity services.

How can I implement cross-filtering by selecting multiple individual dataPoints (either using selection handler, or by impementing a basic/advanced filter)?

2/ To achieve cross filtering when clicking on the age or gender labels, I have used the Basic Filter. This works fine when filtering on a data column (age, filter); but I can't see how to filter on a measure (other than by value) or using a two column filter to select both an age and a gender. The ability to select an individual data point must be possible, but not using the Basic or Advanced filters, and the selection handler approach doesn't permit selection of more than one point.

How can I implement a basic/advanced filter that creates a filter which matches values in two or more columes (e.g. age, and gender) and enables more complex selections (advanced Filter only permits selections from a single column - if I understand correctly)? I also need to have the ability to have a list of complex selections/filters.

More practical may be the ability to create a filter on SelectionID. Is it possible to create a filter by SelectionID? [I may have missed something obvious, but a lot of functionality appears to have disappeared late in 2016 when the API was changed].

3/ When enabling cross-highlighting from population pyramid to other visuals, and then selecting datapoints in the population pyramid, the other visuals are filtered not highlighting. There does not appear to be a way to cross-highlight other visuals from a custom visual, only cross-filtering.

I don't know if this is intended operations, but other visuals are able to cross-highlight. I've enabled highlighting through the user interface; however, is there something in the custom visual that needs to be set so that other visuals can show highlights and are not just simply filtered irrespective of the settings in the user interface?

Whilst the ability to develop custom visuals for Power BI is welcome, the API and documentation is seriously lacking and undermines the product. It should be simple to replicate the selection, filtering and highlighting capabilities of the built in visuals; however, much of the API functionality appears to be missing or incomplete. Where functionality does exist the documentation is poor and the examples are inconsistent and confusing. It also does not help that searching for examples often drags up prior work on the previous API.

Any insight or assistance anyone can provide on selections, filtering and highlighting would be much appreciated.

[dakdgdl]

Hi v-viig

I tried cross-filte by cross-select as below.

This is my coding.

------------
let rowSelection: ISelectionId = this.visualHost.createSelectionIdBuilder()
.withTable(this.dataView.table, rowIndex)
.createSelectionId(); 
await this.selectionManager.select(rowSelection, true).then(result => {
console.log(result);
});

--------------

This is the relevant settings in capabilities.json

------------

"general": {
"displayName": "General",
"properties": {
"selection": {
"displayName": "Selection",
"type": {
"text": true
}
},
"filter": {
"type": {
"filter": true
}
},
"selfFilter": {
"type": {
"filter": {
"selfFilter": true
}
}
}
}
}

-----------------

But this didn't cross-select the passed selectionID row in other visuals of the page.
What could be the reason. Any specific config setting in tsconfig, capabilities.json or webpack.config?

Or

is there a specific way of adding ISelectionId objects of table mapping data to a dataPoints array of visual.

Currently I havent add to dataPoints array, but directly called the method,

this.selectionManager.select(rowSelection, true)

Please advise.
Thanks in advance,
Lasantha

[v-viig]

Hello @diaAzul

1. SelectionManager.select allows to select multiple data points (selectionIds) if the second argument is true
2. Advanced Filter API doesn't support filtering by multiple columns
3. How do you implement selection? Do you use SelectionManager.select?

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[diaAzul]

Thanks, 

1/ I've tried selectionManager as well - that doesn't work for cross filtering more than one data point. Note there is a difference between selection and cross-visual filtering. Interactive services works for selection (i.e. selecting multiple points within the custom visual) but doesn't work for cross-visual filtering within the same report.

2/ Agreed. The question is how can I cross-filter visuals, selecting multiple dataPoints (SelectionId)? Each time I try and implement this it causes PowerBI to throw an error in the other visuals in the report. 

3/ I am using selectionHandler and BindEvents within the interactive services. SelectionHandler is the method on InteractiveServices class; selectionFilter is my own class within which basic and advancced filters are constructed/applied; DataPoint.convert() is a method to extract the datum from the d3 SVGElement.

        public bindEvents(params: ISelectionToolsParams, selectionHandler: ISelectionHandler): void {

            this.params = params;
            this.selectionHandler = selectionHandler;

            this.bindClickEventToClearCatcher();

            this.params.visualDataPoints.on('click', (el: SVGElement): void => {
                this.selectionFilter.reset();
                this.selectionHandler.handleSelection(DataPoint.convert(el), (d3.event as MouseEvent).ctrlKey);
                this.selectionHandler.applySelectionFilter();

                (d3.event as MouseEvent).stopPropagation();
            });

            this.params.categories.on('click', (el: SVGElement): void => {
                if (!(d3.event as MouseEvent).ctrlKey) {
                    this.clearSelection();
                }
                this.selectionFilter.addCondition(this.visFilterColumnTargets.ageRange, 'Is', (el as any));
                this.selectionFilter.apply();

                this.params.visualDataPoints.data().forEach((d: SVGElement) => {
                    const dataPoint: IDataPoint = DataPoint.convert(d);

                    if (dataPoint.age === (el as any)) {
                        this.selectionHandler.handleSelection(dataPoint, true);
                    }
                });

                (d3.event as MouseEvent).stopPropagation();
            });
}

Part of the problem is that PowerBI cusom visual API has not yet matured, it may do so over the next 12-18 months, but for now we are looking at other solutions.

[v-viig]

How do you construct SelectionId? Can you share code snippet?

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[diaAzul]

Thank you @v-viig

The data consists of two category columns and two measure columsn:

ageRange(Category)	Gender(Category)	Value(Measure)	Reference(Measure)

A0-4			Male			5,000		5,500
A0-4			Female			4,500		6,000
A5-9			Male			5,500		5,500
A5-9			Female			4,600		6,000
...
A85+			Female			1,500		900

Selection/Filtering is on both AgeRange and Gender.

SelectionId Code (summarised):

const dataView: DataView  = data.dataViews[0];

const ageRange: DataViewCategoryColumn = dataView.categorical.categories[0];
const gender: DataViewCategoryColumn = dataView.categorical.categories[1];
const valueColumn: DataViewValueColumn = dataView.categorical.values[fieldIndices[interfaceFields.values]];
const refColumn: DataViewValueColumn = dataView.categorical.values[fieldIndices[interfaceFields.reference]];

for (let i: number = 0; i < dataView.categorical.categories[0].values.length; i++) {

	const selectionId: ISelectionId = this.host.createSelectionIdBuilder()
		.withCategory(ageRange, i)
		.withCategory(gender, i)
		.createSelectionId();


	chartViewModel.dataPoints.push(new DataPoint({
		age: <string>ageRange.values[i],
		gender: <string>gender.values[i],
		value: getValues(interfaceFields.values, i) || 0,
		reference: getValues(interfaceFields.reference, i) || 0,
		highlightValue: getHighlights(interfaceFields.values, i) || 0,
		highlightReference: getHighlights(interfaceFields.reference, i) || 0,
		identity: selectionId,
		specificIdentity: selectionId,
		highlighted: isHighlighted,
		selected: false
	}));
}

Link to code in-situ on gitHub

For reference the error message when two or more individual points are selected is included below:

[diaAzul · Accepted solution]

@v-viig

Partial solution to the selection problems (issues 1 and 2), but not the fact that other visuals are filtered not highlighted (3).

The problem stems from the fact that when more than one category column is add to createSelectionIdBuilder() it causes the cross-filtering to error when more than one data point is selected.

The fix is to only add one category column to the createSelectionIdBuilder - it doesn't matter which one, and the values in the column appear to be irrelevant. This is counter intuitive if comparing with an SQL select, or any other filtering function which would look to match against the values in the category columns. The resultant code (showing the removed line commented out) is as follows:

const selectionId: ISelectionId = this.host.createSelectionIdBuilder()
    .withCategory(ageRange, i)
    // .withCategory(gender, i)
    .createSelectionId();

It would be helpful if PowerBI provided a more useful error message, or error checking, to advise that more than one category is not permitted when cross filtering; or even, have a solution which doesn't throw an error when more than one category is selected.

[v-viig]

Do you use categorical data mapping? Are you able to use table instead?

The table mapping provides a row specific selectors. Thatwill allow us to select multiple rows.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[diaAzul]

Ignat,

The chart uses categorical data mapping. This is necessary as the chart is driven by two category columns (ageRange and gender). The ageRange categories determine the categories on the vertical y-axis, and the gender determines whether the data is displayed as a horizontal bar to the left or right of the central y-axis.

Within the chart options, there is a seperate box to enter the ageRange column and another for the gender. If this was to be implemented as a table it would not be easy to identify which column in the table represented the ageRange, the gender and the measures. So, no I do not believe that it would be possible to use a table.

I've looed through the way selections are passed between visuals on the page and the way that selectionId is generated. From my observation, even in a categorical chart, the selectionId is a row based ID. As long as only one category column is used in the create SelectionIDBuilder it doesn't matter which category it is, so long as only one category is used. It would appear that the column chosen is used to identify the table, and then the Id from that table is used to drive the selection process.

The only complication that I haven't tested is where the ageRange category comes from one table (Table A) which is joined with another table (Table B) which contains both ageRange, gender and measure data. If the selectionId is created from ageRange then it will only have selectoinId based on Table A, however, if it is based on gender it will havae selection Id based on Table B. This may affect how selections are then propagated throughout the page. Ihaven't had time to test this yet, but will do so over the next couple of weeks as it may have some implications for the design of the visual and how selections are implemented.

For now the selections work fine, but I can only filter other visuals, I cannot highlight them.

[v-viig]

Selection works as a filter since you use applySelectionFilter method that applies a filter.
Please remove the call of this method to change behavior of selection to highlighting.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[diaAzul]

@v-viigThanks for the suggestion, but that doesn't work. All that happens is cross filtering / highlighting no longer works (i.e. clicking on a selection in the visual highlights the selected element, but there is no impact on the other visuals on the page.

You are probably correct that applySelectionFilter is filtering the other visuals, but just using slectionHandler.handleSelection() is insufficient on its own to communicate change to the other visuals on the page.

[v-viig]

Can you share the capabilities.json?

I suppose there's a filter property at general group.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[diaAzul]

@v-viig: Yes there is a filter property in capabilities.json.

Link to file on github: Capabilities.json

    "objects": {
        "general": {
            "displayName": "General",
            "displayNameKey": "formattingGeneral",
            "properties": {
                "selection": {
                    "displayName": "Selection",
                    "type": {
                        "text": true
                    }
                },
                "filter": {
                    "type": {
                        "filter": true
                    }
                },
                "selfFilter": {
                    "type": {
                        "filter": {
                            "selfFilter": true
                        }
                    }
                }
            }

Is this not correct?

[v-viig]

You should remove the filter property and selectionHandler.applySelectionFilter() if you want handle selection as a highlighting.

Please let me know if that works well.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[Anonymous]

@v-viig so basically whats the aim of using fitler in capabilities.json ? I don't get it.

[v-viig]

The filter propertry must be used if you want to handle selection as filtering.

Csutom filter can be applied if and only if filter property is declared in the capabilities and applySelectionFilter or applyJSONFilter are called.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

[a10k]

Hi, Thank for the info, can you explain what the selfFilter is doing? @v-viig 

[diaAzul]

@v-viig, thank you for those suggestions. I have made the changes and the visual now works as intended.