Del via

Control report filters

  • Artikkel

When you embed a Power BI report, you can apply filters automatically during the loading phase, or you can change filters dynamically after the report is loaded. For example, you can create your own custom filter pane and automatically apply those filters to reports to show the user specific insights. You can also create a button that allows the user to apply filters to the embedded report.

The following filter types are supported:

The filter object attributes

All filter types inherit the IFilter interface. The attributes listed below are relevant for all the filter types.

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

Schema

The $schema attribute defines the type of filter. There are five schemas available, one for each filter type:

  • Basic - https://powerbi.com/product/schema#basic
  • Advanced - https://powerbi.com/product/schema#advanced
  • Relative date - https://powerbi.com/product/schema#relativeDate
  • Relative time - https://powerbi.com/product/schema#relativeTime
  • Top N - https://powerbi.com/product/schema#topN

Display settings

The displaySettings attribute, defines the way the filter is displayed in the filters pane.

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

  • isLockedInViewMode - A locked filter is applied and displayed in the filter pane. The filter value cannot be changed in view mode. Set it to true to lock the filter.

  • isHiddenInViewMode - A hidden filter is applied to the report but not displayed in the filter pane in view mode. Set it to true to hide the filter.

  • displayName - A filter can be displayed in the filter pane with a personalized name. Use this attribute to set a personalized name for your filter. When the value is undefined or null, the default name of the filter will be displayed (typically the name of the filtered data field).

Filter type

The filterType attribute defines the filter's type. Use the following enum, defined in the models library:

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

Target

The target attribute defines the filter's target. For more information, see Use targets to select which data field to act on.

Additional attributes by filter type

Each filter type has its own interface with a different set of attributes. The filter interfaces are part of the powerbi-models library.

Basic filter

Basic filter has a single operator with one or more values.

interface IBasicFilter extends IFilter {
    operator: BasicFilterOperators;
    values: (string | number | boolean)[];
    requireSingleSelection?: boolean;
}

  • operator - For basic filter the operator can be one of the following:

    type BasicFilterOperators = "In" | "NotIn" | "All"

  • values - An array of values for the filter, all values need to be of the same type.

  • requireSingleSelection - Defines whether it is possible to select multiple values on the filter. If it is set to true, only a single value can be selected.

For example:

const basicFilter = {
  $schema: "https://powerbi.com/product/schema#basic",
  target: {
    table: "Store",
    column: "Count"
  },
  operator: "In",
  values: [1, 2, 3, 4],
  filterType: models.FilterType.BasicFilter,
  requireSingleSelection: true
}

Advanced filter

Advanced filter has a single logical operator and one or two conditions that have their own operator and value.

interface IAdvancedFilter extends IFilter {
    logicalOperator: AdvancedFilterLogicalOperators;
    conditions: IAdvancedFilterCondition[];
}

  • logicalOperator - The logical operator can be one of the following:

    type AdvancedFilterLogicalOperators = "And" | "Or";

  • conditions - An array of conditions for the advanced filter, each condition has an operator and a value.

    interface IAdvancedFilterCondition {
    value?: (string | number | boolean | Date);
    operator: AdvancedFilterConditionOperators;
}

    The available operators for a condition are:

    type AdvancedFilterConditionOperators = "None" | "LessThan" | "LessThanOrEqual" | 
"GreaterThan" | "GreaterThanOrEqual" | "Contains" | "DoesNotContain" | "StartsWith" | 
"DoesNotStartWith" | "Is" | "IsNot" | "IsBlank" | "IsNotBlank";

For example:

const advancedFilter = {
  $schema: "https://powerbi.com/product/schema#advanced",
  target: {
    table: "Store",
    column: "Name"
  },
  logicalOperator: "Or",
  conditions: [
    {
      operator: "Contains",
      value: "Wash"
    },
    {
      operator: "Contains",
      value: "Park"
    }
  ],
  filterType: models.FilterType.AdvancedFilter
}

Note

If you're creating an AdvancedFilter with only a single condition, set the logicalOperator to "And". The logical operator is ignored when being parsed by Power BI because there's only one condition, and when the filter is serialized the default logical operator is "And". This ensures the filters returned when calling getFilters, match the ones set using setFilters.

Top N filter

Top N filter has a single operator, item counter for the amount of items to display, and order by target.

interface ITopNFilter extends IFilter {
    operator: TopNFilterOperators;
    itemCount: number;
    orderBy: ITarget;
}

  • operator - The operator for Top N filter can be one of the following:

    type TopNFilterOperators = "Top" | "Bottom";

  • itemCount - The amount of items to display.

  • orderBy - The target data field to sort by. For more information, see Use targets to select which data field to act on.

For example:

const topNFilter = {
  $schema: "https://powerbi.com/product/schema#topN",
  target: {
    table: "Store",
    column: "name"
  },
  operator: "Top",
  itemCount: 5,
  orderBy: {
      table: "Product",
      measure: "Count of Product"
   },
  filterType: models.FilterType.TopN
};

Relative date and relative time filters

The relative date filter and the relative time filter both inherit from the IRelativeDateTimeFilter interface:

interface IRelativeDateTimeFilter extends IFilter {
    operator: RelativeDateOperators;
    timeUnitsCount: number;
    timeUnitType: RelativeDateFilterTimeUnit;
}

  • operator - The operator for relative date and time filters can be one of the following:

    enum RelativeDateOperators {
    InLast = 0,
    InThis = 1,
    InNext = 2,
}

  • timeUnitsCount - The amount of time units.

  • timeUnitType - Defines the unit of time the filter is using.

    enum RelativeDateFilterTimeUnit {
    Days = 0,
    Weeks = 1,
    CalendarWeeks = 2,
    Months = 3,
    CalendarMonths = 4,
    Years = 5,
    CalendarYears = 6,
    Minutes = 7,
    Hours = 8
}

    The following table lists the unit times supported by the relative date and relative time filters.

    Time unit Relative date Relative time
    Days ✔ ✖
    Weeks ✔ ✖
    CalendarWeeks ✔ ✖
    Months ✔ ✖
    CalendarMonths ✔ ✖
    Years ✔ ✖
    CalendarYears ✔ ✖
    Minutes ✖ ✔
    Hours ✖ ✔

  • includeToday - Accepts a boolean value that specifies whether to include the current day in the filter.

    interface IRelativeDateFilter extends IRelativeDateTimeFilter {
includeToday: boolean;
}

    Note

    includeToday is only supported by the relative date filter.

A relative date filter example:

const relativeDateFilter = {
  $schema: "https://powerbi.com/product/schema#relativeDate",
  target: {
    table: "Sales",
    column: "OrderDate"
  },
  operator: models.RelativeDateOperators.InLast,
  timeUnitsCount: 30,
  timeUnitType: RelativeDateFilterTimeUnit.Days,
  includeToday: true,
  filterType: models.FilterType.RelativeDate
};

A relative time filter example:

const relativeTimeFilter = {
  $schema: "https://powerbi.com/product/schema#relativeTime",
  target: {
    table: "Sales",
    column: "OrderDate"
  },
  operator: models.RelativeDateOperators.InLast,
  timeUnitsCount: 12,
  timeUnitType: models.RelativeDateFilterTimeUnit.Hours,
  filterType: models.FilterType.RelativeTime
};

Filters APIs

Use the following methods to get and update the filters applied to a report:

Get filters

Use getFilters to get all the filters for one of the following objects:

  • Report
  • Page
  • Visual
getFilters(): Promise<IFilter[]>

Update filters

Use updateFilters to add, replace or remove filters on the object (report, page or visual). Receives an operation and an optional filters array.

updateFilters(operation: models.FiltersOperations, filters?: models.IFilter[]): Promise<IHttpPostMessageResponse<void>>

Filters operation

When calling updateFilters you need to pass the filter operation you want to preform. The available operations are:

  • RemoveAll - Removes all filters on the filter level.
  • ReplaceAll - Replaces all existing filters on the filter level with the given filters.
  • Add - Adds the given filters on the filter level (in addition to the existing filters).
  • Replace - Replaces an existing filter with a given filter only if both filters apply to the same data field. If there is a given filter that doesn't replace an existing filter, it will be added.

Note

When calling the API with RemoveAll, the filters argument must be undefined. For any other operations, it must be defined.

Filters levels

Updating and getting the filters can be done at three levels. Filters on different level are independent, and updating filters on one level, will not change another. For example, removing a page filter, doesn't remove it from other pages in the report.

The supported levels for filters are:

  • Report - The filters are applied to all the pages in the report.
  • Page - The filters are applied to the current report page.
  • Visual - The filters are applied to a specific visual.

Note

Only visual level filters support the ITopNFilter filter type.

Report level filters

To get or update the filters that apply to all the pages in the report, call the relevant API on the report object. For example:

Get the report filters

Getting the filters applied to all pages.

let reportFilters = await report.getFilters();

Add new filters to the report filters

Adding new filters, alongside the existing filters, for all pages.

await report.updateFilters(models.FiltersOperations.Add, filtersArray);

Remove the report filters

Remove the filters applied to all pages.

await report.updateFilters(models.FiltersOperations.RemoveAll);

Page level filters

To get or update page level filters, do the following:

  1. Get the page object for the target page. For more information, see Get pages and visuals.
  2. On the page object, call the relevant API.

Get the page filters

Getting the filters applied to a specific page.

let reportFilters = await page.getFilters();

Replace all the page filters

Replacing all existing filters applied to a specific page, with a new set of filters.

await page.updateFilters(models.FiltersOperations.ReplaceAll, filtersArray);

Remove the page filters

Removing the filters applied to a specific page.

await page.updateFilters(models.FiltersOperations.RemoveAll);

Visual level filters

To get or update visual level filters, do the following:

  1. Get the visual object for the target visual. For more information, see Get pages and visuals.
  2. On the visual object, call the relevant API.

Get the visual filters

Getting the filters applied to a specific visual.

let reportFilters = await visual.getFilters();

Replace visual filters with same target

Replacing the filters of a specific visual. If a filter exists with the same target data field as a given filter, it is replaced by the given filter. Given filters that don't match any existing filter are added.

await visual.updateFilters(models.FiltersOperations.Replace, filtersArray);

Remove the visual filters

Removing the filters applied to a specific visual.

await visual.updateFilters(models.FiltersOperations.RemoveAll);

Considerations and limitations

  • Having more than two conditions when building an Advanced filter may cause undefined behavior.

  • IncludeExclude and Tuple filter types are not supported.

  • Tuple and hierarchy filter targets are not supported.

Related content