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 Fields pane that is accessible in Report Editing View
  • Filter values can either be obtained from the Visualizations and 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 Fields pane, you will see table and column names
  2. Join the table and column name with a period, like  table.column (no space between the names)
    • This is the Filter name to use when building External Reports in Metric Insights
    • See the Daily Sales 2016.сhannel example in the screenshots below (See the Configure Filter  section of this article)
  3. Filter values are displayed in the Visualizations and Filters pane. You can use these Filter values when adding them manually to External Reports (For more information, see Enter Filter Values Manually)

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. [+ 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: represents a regular Power BI Filter
    • Slicer: is a more user-friendly Filter type which serves for displaying Power BI Slicers in External Report
  4. Select whether the Filter is a "Single Value" or "Multi-Value"

Both Regular Filters and Slicers are configured in the same way. Proceed to the next step to provide 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).

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
  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. If your Filter is multi-value, you can select "Pass All Values"
  4. Select a default value for a single value Filter, and one or more values for a multi-value Filter
  5. [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. Optionally, continue adding filters by clicking [+Add Mapping]. It is not required to set User Map columns for all defined filters
  5. 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?

Beginning v6.4.2 it is possible to limit the scope of sticky filters 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]
  6. If you do not want to see the warning next time, activate Don't warn me again checkbox
  7. [Proceed]