Help & Documentation
Open Support Ticket

Filtering in Power BI Cloud External Reports (v7.2.1+)

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

Beginning in v7.2.1, Filter Names and Values can be retrieved directly from the Power BI Reports and Paginated Reports. There are 2 available workflows of adding Filters to your External Report:

See Choosing Workflow to understand what options each workflow provides, and how they can be combined.

PREREQUISITES:                                          

TABLE OF CONTENTS:

NOTE: For information on filtering External Reports for Power BI Cloud in MI versions prior to v7.2.1, see Filtering in Power BI Cloud External Reports.

Choosing a Workflow

NOTE: The two workflows produce filters that coexist on the same External Report: auto-discovered filters can sit alongside hand-authored ones, and an auto-discovered filter can be edited afterwards to switch its Value Source.

Metric Insights provides two workflows for putting Power BI filters on an External Report:

WorkflowWhen to use

A: Retrieve Filter Names from Microsoft Power BI Cloud (recommended)

You want MI's filter UI to mirror the source report. MI discovers all filters defined on the Report and creates them for you, with live values pulled from Power BI.  

B: Add External Filter manually

  • You need a filter the Report doesn't currently expose, 
  • You want values from a non-Power-BI source (an MI Dataset, Dimension, User Map, or hand-typed list).

Workflow A: Retrieve Filter Names from Microsoft Power BI Cloud

1. Retrieve Filter Names

Access External Report Editor > Configuration tab

Beginning in v7.2.1, you can retrieve Filter Names and Values from Power BI, instead of adding them manually.

  1. [Manage Filters]
  2. Enable [Retrieve Filter Names from Microsoft Power BI Cloud]

2. Refresh Filter Values per Filter

For each retrieved Filter:

  1. Click on the Filter name.
  2. [Refresh Filter Values]
  3. The retrieved values are displayed in the Values grid.
  4. [Save]

NOTES:

  • When a filter card or report parameter was added, renamed, or removed on the source Report, click [Refresh Filters] again. New filters are added; existing filters are matched by Name and keep their defaults, display masks, and User Map bindings. Filters removed from the source are not auto-deleted, use the trashbin icon to clean up.
  • When the data values behind a Filter changed; e.g., a new Region appears, but the Filter card itself is unchanged, click [Refresh Filter Values] in the affected Filter's editor. Only that Filter's picklist is re-pulled, no full re-discovery is needed.

Workflow B: Add External Filter Manually

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:
    • Auto: MI infers the data type from the values it loads (recommended).    
    • String: Treat all values as text.
    • Integer: Treat all values as whole numbers (use this when Power BI rejects quoted numeric values).          

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

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

3.1. Load Filter Values from Microsoft Power BI Cloud

  1. Filter Values: Load Filter Values from Microsoft Power BI Cloud
  2. [Refresh Filter Values]
  3. Sort: Select how you want to sort the Values
    • Ascending/Descending work based on alphabetical order
  4. Optionally, restrict certain Filter Values to specific Users with the "Constrain via User Map" setting.
  5. [Save]
3.2. 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.3. 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.4. 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]