Filter API
Custom Visual Filters API
The article is outdated, read updated article about filter api in official documentation of Power BI.
Filter-visuals allow to filter data. The main difference from selections is that other visuals will be filtered in any way despite highlight support by other visual.
To enable filtering for the visual, the visual should contain filter
object in general
section of capabilities.json content.
"objects": {
"general": {
"displayName": "General",
"displayNameKey": "formattingGeneral",
"properties": {
"filter": {
"type": {
"filter": true
}
}
}
}
}
Filter API interfaces are available in powerbi-models
package. The package also contains classes to create filter instances.
npm install powerbi-models --save
If you use old version tools (version less than 3.x.x) you should include powerbi-models
into the visuals package. Short guide how to include the package
All filters extend IFilter
interface.
export interface IFilter {
$schema: string;
target: IFilterTarget;
}
target
- is table column on datasource.
Basic filter API
Basic filter interface is
export interface IBasicFilter extends IFilter {
operator: BasicFilterOperators;
values: (string | number | boolean)[];
}
operator
- is enumeration with values “In”, “NotIn”, “All”
values
- are values for condition
Example of basic filter:
let basicFilter = {
target: {
column: "Col1"
},
operator: "In",
values: [1,2,3]
}
The filter means “give me all rows where col1
equals to one of values 1,2 or 3”.
SQL equivalent is
SELECT * FROM table WHERE col1 IN ( 1 , 2 , 3 )
To create a filter you can use BasicFilter class in powerbi-models
.
If you use the old version of tools you should get an instance of models in window object by window['powerbi-models']
:
let categories: DataViewCategoricalColumn = this.dataView.categorical.categories[0];
let target: IFilterColumnTarget = {
table: categories.source.queryName.substr(0, categories.source.queryName.indexOf('.')),
column: categories.source.displayName
};
let values = [ 1, 2, 3 ];
let filter: IBasicFilter = new window['powerbi-models'].BasicFilter(target, "In", values);
The visual invokes the filter using the method applyJsonFilter() on the host interface IVisualHost provided to the visual in the constructor.
visualHost.applyJsonFilter(filter, "general", "filter", FilterAction.merge);
Advanced filter API
The Advanced Filter API enables complex cross-visual data-point selection/filtering queries based on multiple criteria (such as “LessThan”, “Contains”, “Is”, “IsBlank”, etc.).
The filter was introduced in Custom Visuals API 1.7.0.
Advanced Filter API also requires target
with table
and column
name. But Advanced Filter API operators are "And" | "Or"
.
Additionally, the filter uses conditions instead of values with interface::
interface IAdvancedFilterCondition {
value: (string | number | boolean);
operator: AdvancedFilterConditionOperators;
}
Condition operators for operator
parameter are "None" | "LessThan" | "LessThanOrEqual" | "GreaterThan" | "GreaterThanOrEqual" | "Contains" | "DoesNotContain" | "StartsWith" | "DoesNotStartWith" | "Is" | "IsNot" | "IsBlank" | "IsNotBlank"
let categories: DataViewCategoricalColumn = this.dataView.categorical.categories[0];
let target: IFilterColumnTarget = {
table: categories.source.queryName.substr(0, categories.source.queryName.indexOf('.')), // table
column: categories.source.displayName // col1
};
let conditions: IAdvancedFilterCondition[] = [];
conditions.push({
operator: "LessThan",
value: 0
});
let filter: IAdvancedFilter = new window['powerbi-models'].AdvancedFilter(target, "And", conditions);
// invoke the filter
visualHost.applyJsonFilter(filter, "general", "filter", FilterAction.merge);
SQL equivalent is
SELECT * FROM table WHERE col1 < 0;
Complete sample code of using Advanced Filter API can be found in sampleslicer visual repository.
Tuple filter API (Multi column filter)
Tuple Filter API was introduced in Custom Visuals API 2.3.0.
Tuple filter API is similar to Basic filter, but it allows defining conditions for several columns and tables.
And a filter has interface:
interface ITupleFilter extends IFilter {
$schema: string;
filterType: FilterType;
operator: TupleFilterOperators;
target: ITupleFilterTarget;
values: TupleValueType[];
}
target
is an array of columns with table names:
declare type ITupleFilterTarget = IFilterTarget[];
The filter can address columns from different tables.
$schema
is “http://powerbi.com/product/schema#tuple”
filterType
is FilterType.Tuple
operator
only allows to use "In"
operator
values
is an array of value tuples, where each tuple represents one permitted combination of the target column values
declare type TupleValueType = ITupleElementValue[];
interface ITupleElementValue {
value: PrimitiveValueType
}
Complete example:
let target: ITupleFilterTarget = [
{
table: "DataTable",
column: "Team"
},
{
table: "DataTable",
column: "Value"
}
];
let values = [
[
// the 1st column combination value (aka column tuple/vector value) that the filter will pass through
{
value: "Team1" // the value for `Team` column of `DataTable` table
},
{
value: 5 // the value for `Value` column of `DataTable` table
}
],
[
// the 2nd column combination value (aka column tuple/vector value) that the filter will pass through
{
value: "Team2" // the value for `Team` column of `DataTable` table
},
{
value: 6 // the value for `Value` column of `DataTable` table
}
]
];
let filter: ITupleFilter = {
$schema: "http://powerbi.com/product/schema#tuple",
filterType: FilterType.Tuple,
operator: "In",
target: target,
values: values
}
// invoke the filter
visualHost.applyJsonFilter(filter, "general", "filter", FilterAction.merge);
Order of column names and values of condition are sensitive.
SQL equivalent is
SELECT * FROM DataTable WHERE ( Team = "Team1" AND Value = 5 ) OR ( Team = "Team2" AND Value = 6 );
Restoring JSON Filter from DataView
Starting from API 2.2 JSON Filters can be restored from VisualUpdateOptions
export interface VisualUpdateOptions extends extensibility.VisualUpdateOptions {
viewport: IViewport;
dataViews: DataView[];
type: VisualUpdateType;
viewMode?: ViewMode;
editMode?: EditMode;
operationKind?: VisualDataChangeOperationKind;
jsonFilters?: IFilter[];
}
Sample JSON Filter
Clear JSON Filter
Filter API accepts null
value of filter as reset or clear
// invoke the filter
visualHost.applyJsonFilter(null, "general", "filter", FilterAction.merge);