Power BI relative date filter scripts

Date filter methods are used to filter data collected over a specified date period. Relative date filters can be set at the Power BI report and page level.

SetRelativeDateReportFilter()

A relative date report filter script can be specified in layout scripts.

SetRelativeDateReportFilter(string tableName, string columnName, string filterOperator, string timeUnitsCount, string timeUnitsType, bool includeToday, string displayName, bool isHiddenInViewMode, bool isLockedInViewMode, bool update = false)

SetRelativeDateReportFilter() Parameters

The following list shows the parameters required to set relative date report filters.

• 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.

• filterOperator

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

  • 0 = InLast

  • 1 = InThis

  • 2 = InNext

    The single digit value of filterOperator specifies the type of date and time operator.

• timeUnitsCount

  The number of time units.

• timeUnitsType

  The unit of time the filter is using, as shown below:

  • 0 = Days

  • 1 = Weeks

  • 2 = CalendarWeeks

  • 3 = Months

  • 4 = CalendarMonths

  • 5 = Years

  • 6 = CalendarYears

  • 7 = Minutes

  • 8 = Hours

• includeToday

  Boolean value that specifies whether to include the current day in the filter or not. When set to true, the current day is included in the date filter.

• 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.SetRelativeDateReportFilter("Sales","OrderDate", "0","2","5",true, "MyFilter3",true, false);

This statement filters order information from the Sales table in the last two years and includes today's data.

SetRelativeDatePageFilter()

A basic relative date filter can also be applied to a Power BI report page. The page name must be passed in as the first parameter.

SetRelativeDatePageFilter(string pageName, string tableName, string columnName, string filterOperator, string timeUnitsCount, string timeUnitsType, bool includeToday, string displayName, bool isHiddenInViewMode, bool isLockedInViewMode, bool update = false)

SetRelativeDatePageFilter() Parameters

The RelativeDatePageFilter method includes the same parameters as the SetRelativeDateFilter() 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.

• filterOperator

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

  • 0 = InLast

  • 1 = InThis

  • 2 = InNext

    The single digit value of filterOperator specifies the type of date and time operator.

• timeUnitsCount

  The number of time units.

• timeUnitsType

  The unit of time the filter is using, as shown below:

  • 0 = Days

  • 1 = Weeks

  • 2 = CalendarWeeks

  • 3 = Months

  • 4 = CalendarMonths

  • 5 = Years

  • 6 = CalendarYears

  • 7 = Minutes

  • 8 = Hours

• includeToday

  Boolean value that specifies whether to include the current day in the filter or not. When set to true, the current day is included in the date filter.

• 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.SetRelativeDatePageFilter("ReportSec3","Sales","OrderDate", "0","2","5",false, "MyFilter4",true, false, true);

This statement filters order information from the Sales table over the last two years and does not include today's data. The filtered data appears on the ReportSec3 page of the Power BI report. The filter has been assigned the name MyFilter4 and is hidden on the Power BI report Filter area. The filter is unlocked and a new filter can be added because the update parameter is true.