Power BI advanced filter scripts

An advanced filter API sets an advanced filter on a Power BI report or a page.

SetAdvancedReportFilter()

An advanced filter contains a single logical operator and one or two conditions that have their own operator and value. An advanced filter script can be specified in layout scripts.

SetAdvancedReportFilter(string tableName, string columnName, string logicalOperator, string operator1, string value1, string operator2, string value2, string displayName, bool isHiddenInViewMode, bool isLockedInViewMode, bool update = false)

SetAdvancedReportFilter() Parameters

• tableName

  Name of the Power BI report table containing the data to be filtered in the report.

• columnName

  Name of the column within the Power BI report table containing the data to be filtered in the report.

• logicalOperator

  The logical operator can be one of the following Boolean operators:

  "And" | "Or";

• conditions

  An array of conditions for the advanced filter. Each condition has an operator and a value specified in the form string operator1, string value1, string operator2, string value2.

  "None" | "LessThan" | "LessThanOrEqual" | "GreaterThan" | "GreaterThanOrEqual" | "Contains" | "DoesNotContain" | "StartsWith" | "DoesNotStartWith" | "Is" | "IsNot" | "IsBlank" | "IsNotBlank"

• displayName

  A filter can be displayed in the filter pane with a personalized name. When the value is undefined or null, the default name of the filter will be displayed (typically the name of the filtered data field).

• isHiddenInViewMode

  Boolean value that determines if a hidden filter is applied to the report but not displayed in the filter pane in view mode. Set to true to hide the filter.

• isLockedInViewMode

  Boolean value that determines if a filter lock is applied and displayed in the filter pane. The filter value cannot be changed in view mode. Set to true to lock the filter.

• update

  Boolean value that determine whether multiple filters can be set. When update is true, a new filter will be added to the existing filters. If set to false, the existing filters are replaced.

Example

MyContent.PBIViewerCntrl.SetAdvancedReportFilter("Time","FiscalMonth","Or","Contains","Mar","Contains","Apr);

This statement filters data from the Time table for the months of March or April.

SetAdvancedPageFilter()

An advanced page filter can also be applied to a report page. The page name of the Power BI report must be passed in as the first parameter.

SetAdvancedPageFilter(string pageName, string tableName, string columnName, string logicalOperator, string operator1, string value1, string operator2, string value2, string displayName, bool isHiddenInViewMode, bool isLockedInViewMode, bool update = false)

SetAdvancedPageFilter() Parameters

The SetAdvancedPageFilter() method includes the same parameters as the SetAdvancedReportFilter() method and also includes the pageName parameter.

• pageName

  Name of the Power BI report page that displays the data to be filtered in the report.

• tableName

  Name of the Power BI report table containing the data to be filtered in the report.

• columnName

  Name of the column within the Power BI report table containing the data to be filtered in the report.

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

  "And" | "Or";

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

  "None" | "LessThan" | "LessThanOrEqual" | "GreaterThan" | "GreaterThanOrEqual" | "Contains" | "DoesNotContain" | "StartsWith" | "DoesNotStartWith" | "Is" | "IsNot" | "IsBlank" | "IsNotBlank"

• displayName

  A filter can be displayed in the filter pane with a personalized name. When the value is undefined or null, the default name of the filter will be displayed (typically the name of the filtered data field).

• isHiddenInViewMode

  Boolean value that determines if a hidden filter is applied to the report but not displayed in the filter pane in view mode. Set to true to hide the filter.

• isLockedInViewMode

  Boolean value that determines if a filter lock is applied and displayed in the filter pane. The filter value cannot be changed in view mode. Set to true to lock the filter.

• update

  Boolean value that determine whether multiple filters can be set. When update is true, a new filter will be added to the existing filters. If set to false, the existing filters are replaced.

Example

MyContent.PBIViewerCntrl.SetAdvancedPageFilter("Time","FiscalMonth","Or","Contains","Mar","Contains","Apr);

This example shows data from the FiscalMonth table for the months of March or April.