Filtering in Power BI Cloud External Reports

This article provides details on how to configure filtering in Power BI Cloud External Reports.

PREREQUISITES:

Establish Connectivity to Microsoft Power BI

Table of contents:

How to Find Filter Names in Microsoft Power BI?

Where to find FILTERS and FILTER VALUES:

  • Power BI Filters are applied to specific Fields in data Tables.
  • Filter names can be found in the Data pane that is accessible in Report Editing View
  • Filter values can either be obtained from the Filters pane in Report Editing View or Filters pane in Report Reading View

Filters from Microsoft Power BI are passed to Metric Insights in the following format: 'table'[column]

1. Access Report Editing View

From the report, click "Edit Report".

2. Identify Filter names in Power BI

[6.2.4] Slicers are now supported as a Filter Type for Microsoft Power BI Cloud External Reports.

When in Report Edit mode:

  1. In the Data pane, you will see table and column names
  2. Join the table and column name in the following format: 'table'[column] (see the example on the screenshot)
    • This is the Filter name to use when building External Reports in Metric Insights
    • See the 'Daily Sales 2016'[product_category] example in the Configure Filter section of the article
  3. Filter values are displayed in the Filters pane. You can use these Filter values when adding them manually to External Reports

How to Add Microsoft Power BI Filters to Metric Insights External Report?

When creating an External Report fetched from Microsoft Power BI, after you define the Report that should serve as a Data Source, you may pre-filter the information that is going to be fetched.

1. Add a New Filter

Access the Configuration tab of your External Report

  1. [Manage Filters]
  2. [+ Add External Filter]

2. Configure Filter

  1. Microsoft Power BI Cloud Filter Name: Define the name of the Filter from Microsoft Power BI
  2. Display Name: Specify a name for the Filter value which will be displayed in the External Report Viewer
  3. Select the Filter Type:
    • Regular: A regular Power BI Filter
    • Slicer: A more user-friendly Filter type which serves for displaying Power BI Slicers in External Report

NOTE: Both Regular Filters and Slicers are configured in the same way.

  1. Select whether the Filter is:
    • Single Value: Only one value can be selected
    • Multi-Value: Multiple values can be selected
    • Date Picker: A calendar from which you can select a date to filter Report display
    • Text: Filter values are entered as text
  2. Select Filter Value Type

Proceed to the next step to provide Filter Values for the chosen Filter Type.

3. Provide Filter Values

NOTE: The Values provided in an External Report Filter must exactly match the Filter Values in Power BI (if the values do not match, the Filter will not work).

There are three ways of providing Filter values for External Reports, see the corresponding sections for details:

3.1. Map to Dataset Values
  1. Filter Values: "Dataset"
  2. Sort: Select the sorting order for the Filter Values
  3. Dataset & View: Select the Dataset's View and its Value Column whose Values will be mapped to Power BI filters
  4. Parent Filter: Choose the parent Filter
  5. [Save]
3.2. Map to Dimension Values
  1. Filter Values: "Map to Dimension Values"
  2. Sort: select the sorting order for the Filter Values
  3. Dimension: select a corresponding Dimension from the drop-down list. All its Values are loaded to the Values list automatically
    • This Dimension should have already been created in Metric Insights. For reference, see Basic Dimensions
  4. [Save] 
3.3. Enter Manually
  1. Filter Values: "Enter Manually"
  2. [Save]
  3. Click [+Add Value], enter the Filter Value in the opened pop-up, then [Save]
  4. [Save]

4. Set Defaults for your Filters

Configuring defaults allows content builders or administrators to define Filter defaults for users viewing the External Report. Defaults may be set the same for everyone, or applied based on a User Map. Consequently, any number of Users will have the correct Filter Values when they open their External Report.

4.1. Set Defaults Same for Everyone

  1. Select "Same for everyone"
  2. Click on the pencil icon to edit the Filter
  3. Set Filter: "Yes"
  4. If your Filter is multi-value, you can select "Pass All Values" to select all values
  5. Select the Default Value(s)
  6. [Save]

4.2. Set Defaults Using User Map

Setting the Defaults via a User Map does not restrict Users from viewing  non-default values. It simply sets the Values displayed upon initial viewing. If a User is not in the User Map, the system will initially  display the View with no default values.

  1. Select "Apply based on User Map"
  2. Choose a User Map
  3. Match the BI tool Filter to a User Map Column
  4. Click [View], the entered data is saved automatically

5. Delete Filters

To delete a Filter:

  1. [Manage Filters]
  2. Click the trashbin icon in the respective row

How to Configure the Scope of Sticky Filters?

NOTE: To enable Sticky Filters, set the value of ENABLE_POWERBI_STICKY_FILTERS System Variable to 'Y'.

The scope of sticky filters can be limited by using the variable LIMIT_POWERBI_STICKY_FILTER_SCOPE. When enabled, this variable overrides the standard behavior of sticky filters so that the filter selection is remembered only within a single External Report.

To enable the variable, access Admin > System > System Variables.

  1. Enter LIMIT_POWERBI_STICKY_FILTER_SCOPE in the search field
  2. Click the gear icon next to the variable
  3. Set the Assigned Value to 'Y'
  4. [Save]
  5. [Commit Changes]