Skip to content

Filters

Jump to bottom
submarine-launched edited this page Dec 2, 2019 · 25 revisions

As you may have read from the Embed Configuration Details page, you can apply filters when loading a report.  You can also change filters dynamically. This allows you to create your own custom filter pane to match the brand of your application, and to automatically apply filters on some other context of your application to help show the user the correct visual/information.

Report Level Filters

Retrieving filters:

report.getFilters()
 .then(filters => {
     ...
 });

Applying filters: (See section below on Constructing Filters)

const filter = { ... };

report.setFilters([filter])
  .catch(errors => {
    // Handle error
  });

Removing filters:

report.removeFilters();

Report level filter supports the following types: IBasicFilter | IAdvancedFilter | IRelativeDateFilter. see samples below for more information.

export type ReportLevelFilters = IBasicFilter | IAdvancedFilter | IRelativeDateFilter;

Filter Types

export enum FilterType {
  Advanced = 0,
  Basic = 1,
  Unknown = 2,
  IncludeExclude = 3,
  RelativeDate = 4,
  TopN = 5,
  Tuple = 6
}

Page Level and Visual Level Filters

As you may have read in Understanding the Object Hierarchy report, page, and visual objects all implement the IFilterable interface which means each of these objects can also use getFilters, setFilters, and removeFilters just like reports.

It's important to understand that all of these filters are independent and adding or removing to one level does not implicitly change another.

In order to set page level and/or visual level filters, the requested page/visual instance is needed,

* For retrieving a Page instance follow one of the methods on Page Navigation.

* For retrieving a Visual instance follow Get Visuals.

Page Level Filters

Retrieving filters:

page.getFilters()
 .then(filters => {
     ...
 });

Applying filters: (See section below on Constructing Filters)

const filter = { ... };

page.setFilters([filter])
  .catch(errors => {
    // Handle error
  });

Removing filters:

page.removeFilters();

Page level filter supports the following types: IBasicFilter | IAdvancedFilter | IRelativeDateFilter. see samples below for more information.

export type PageLevelFilters = IBasicFilter | IAdvancedFilter | IRelativeDateFilter;

Visual Level Filters

Visuals automatically have auto filter for each of the visual's dimensions, these filters will be returned on getFilters and will not be removed by setFilters or removeFilters

Retrieving filters:

visual.getFilters()
 .then(filters => {
     ...
 });

Applying filters: (See section below on Constructing Filters)

const filter = { ... };

visual.setFilters([filter])
  .catch(errors => {
    // Handle error
  });

Removing filters:

visual.removeFilters();

Visual level filter supports the following types: IBasicFilter | IAdvancedFilter | IRelativeDateFilter | ITopNFilter | IIncludeExcludeFilter. see samples below for more information.

export type VisualFilterTypes = IBasicFilter | IAdvancedFilter | IRelativeDateFilter | ITopNFilter | IIncludeExcludeFilter;

Note: Setting and getting the filter applied by a slicer programmatically, is currently not available.

Constructing Filters

Filters are javascript objects that have a special set of properties.  Currently, there are five types of filters: Basic, Advanced, Relative Date, Top N and Include/Exclude, which match the types of filters you can create through the filter pane. There are corresponding interfaces IBasicFilter, IAdvancedFilter, IRelativeDateFilter, ITopNFilter and IIncludeExcludeFilter, which describe their required properties. These interfaces come from the powerbi-models repo where you can find more details.

Report, Page and Visual level filter supports Basic, Advanced and Relative Date filters. In addition to these filters, Visual Level filter also supports Top N and Include/Exclude filters.

Sample of filters:

const basicFilter: pbi.models.IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Store",
    column: "Count"
  },
  operator: "In",
  values: [1,2,3,4],
  filterType: 1 // pbi.models.FilterType.BasicFilter,
  requiresSingleSelect: true // Limits selection of values to one.
}

Note: requiresSingleSelect will be available for pre-applied filters (filters on load) with embed config in the coming month.

const advancedFilter: pbi.models.IAdvancedFilter = {
  $schema: "http://powerbi.com/product/schema#advanced",
  target: {
    table: "Store",
    column: "Name"
  },
  logicalOperator: "Or",
  conditions: [
    {
      operator: "Contains",
      value: "Wash"
    },
    {
      operator: "Contains",
      value: "Park"
    }
  ],
  filterType: 0 //pbi.models.FilterType.AdvancedFilter
}
const relativeDateFilter: pbi.models.IRelativeDateFilter = {
  $schema: "http://powerbi.com/product/schema#relativeDate",
  target: {
    table: "Sales",
    column: "OrderDate"
  },
  operator: pbi.models.RelativeDateOperators.InLast,
  timeUnitsCount: 30,
  timeUnitType: pbi.models.RelativeDateFilterTimeUnit.Days,
  includeToday: true,
  filterType: 4 // pbi.models.FilterType.RelativeDate
};
const topNFilter: pbi.models.ITopNFilter = {
  $schema: "http://powerbi.com/product/schema#topN",
  target: {
    table: "Store",
    column: "name"
  },
  operator: "Top",
  itemCount: 5,
  orderBy: {
      table: "Product",
      measure: "Count of Product"
   },
  filterType: 5 // pbi.models.FilterType.TopN
};

[IIncludeExcludeFilter below is currently not supported]

const includeExcludeFilter: models.IIncludeExcludeFilter = {
  $schema: "http://powerbi.com/product/schema#includeExclude",
  target: {
    table: "Sales",
    column: "Category"
  },
  values: ["Games and Toys"],
  isExclude: true,
  filterType: 3 // pbi.models.FilterType.IncludeExclude
};

Differences between the types of filters

- Basic filters have a single operator with one or more values - Advanced filters have a single logical operator and accept one or two conditions that have their own operator and value. - Relative Date filters have a single logical operator, time units counter, time unit type and include today field.  - Top N filters have a single operator and item counter for the amount of items to display. - Include/Exclude filters have values (data points) to include or exclude

There is a constructor function for each type: pbi.models.BasicFilter, pbi.models.AdvancedFilter, pbi.models.RelativeDate, pbi.models.TopN and pbi.models.IncludeExclude, which help with the creation of these objects.

Note: If you are creating an AdvancedFilter with only a single condition then the logicalOperator should be set to "And".  The reason is that the logical operator is ignored when being parsed by Power BI because there is only one condition and when it is serialized it will default to "And". This ensures the filters returned when calling getFilters match the ones you set using setFilters.  The AdvancedFilter helper method will help ensure you follow this practice.

const advancedFilter = new pbi.models.AdvancedFilter({
     table: "Store",
     column: "Name"
  }, "And", [
  {
    operator: "Contains",
    value: "Wash"
  },
  {
    operator: "Contains",
    value: "Park"
  }
]);

Advanced filter pane capabilities

The following section is relevant only when the Power BI new filter pane experience is enabled for the report.

This pane support features which are applied per filter:

  • Hide filter: a hidden filter is applied to the report but not displayed in the filter pane in 'View Mode'.
  • Lock filter : a locked filter is applied and displayed in the filter pane but the filter value cannot be changed in 'View Mode'.
  • Rename filter: a filter can be displayed in the filter pane with a custom name.

The state of each filter is represented by the displaySettings property of the powerBIModels.IFilter interface. It can be get and set using the getFilters and setFilters APIs respectively. The displaySettings object has the following optional properties:

  • isLockedInViewMode: if true, the filter is locked
  • isHiddenInViewMode: if true, the filter is hidden
  • displayName: the name displayed in the filter pane for the filter. When the value is undefined or null, the default name of the filter will be displayed (typically the name of the filtered field)

IFilterDisplaySettings

export interface IFilter {
  $schema: string;
  target: IFilterGeneralTarget;
  filterType: FilterType;
  displaySettings?: IFilterDisplaySettings;
}

export interface IFilterDisplaySettings {
  isLockedInViewMode?: boolean;
  isHiddenInViewMode?: boolean;
  displayName?: string;
}

Full code example

// setting 2 report filters, the first locked with a display name, the second hidden
const lockedBasicFilter: pbi.models.IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Store",
    column: "Count"
  },
  operator: "In",
  values: [1,2,3,4],
  filterType: 1 // pbi.models.FilterType.BasicFilter,
  displaySettings: {
    isLockedInViewMode: true,
    displayName: "Number of stores"
  }
};

const hiddenBasicFilter: pbi.models.IBasicFilter = {
  $schema: "http://powerbi.com/product/schema#basic",
  target: {
    table: "Date",
    column: "Year"
  },
  operator: "In",
  values: [2019],
  filterType: 1 // pbi.models.FilterType.BasicFilter,
  displaySettings: {
    isHiddenInViewMode: true
  }
};

report.setFilters([lockedBasicFilter, hiddenBasicFilter])
.catch(errors => {
  // Handle error
});
Clone this wiki locally