Pre-filtering Power BI data for External Reports

Before collecting data from Microsoft Power BI objects, you can pre-filter it. This allows you to focus on the exact information that is required for analysis and exclude the data that is irrelevant for you and your research.

PREREQUISITES:

Establish Connectivity to Microsoft Power BI

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

Zoom:

From the report, click "Edit Report".

2. Identify Filter names in Power BI

Zoom:

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.Channel example in the screenshots below (View Section 1 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, go to Section 1.1).  

1. Adding Microsoft Power BI Filters to Metric Insights

Zoom:

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 information that is going to be fetched.

To do so, click [+ Microsoft PowerBI Filter]. Next, you can choose whether you are going to define filters manually or via the existing Dimension Values.

1.1. Enter manually

Zoom:
  1. Microsoft PowerBI Filter Name: Define the name of the filter from Microsoft Power BI
  2. Filter Values: Choose "Enter Manually"
  3. Save to proceed
  4. Click [+Add Value], and in the opened pop-up, type in the name of the filter value. Save your entry.
  5. Save your entries

1.2. Using Dimension Values

Zoom:
  1. Microsoft PowerBI Filter Name: Define the name of the Filter from Microsoft Power BI
  2. Filter Values: Choose "Map to Dimension Values"
  3. Dimension: select a corresponding Dimension from the drop-down list and all its Values are going to be loaded to the Values list automatically
    • This Dimension should have already been created in Metric Insights. For reference, see Basic Dimensions
  4. Save your entries

NOTE: Dimensions used here must have Values that exactly match the Filter Values in Microsoft Power BI (if the Values do not match, the Filter will not work). Identify Filter names in Power BI explains where you can find Filter Values.

2. How do I add filters to a results set from Power BI?

Zoom:
  1. Click the Pencil icon in the Filter field to open configuration settings
  2. When a Filter is added, you can use it for "All Values", "Only Selected Values" or ignore it

3. Deleting Filters

Zoom:

To delete a Filter:

  1. Click the Filter icon in the Report field
  2. Click the Trashbin icon in the respective row