Smart API parametric filters are implemented as OData Actions (which are not implicitly supported by Power BI). However, the connector provides a special Power Query function to support execution of Smart API parametric filters.
The following steps show you how to configure and execute parametric filters using the Power Query function provided with the connector.
-
After you connect to a Smart API, the Navigator dialog box shows all available OData entity sets, as well as a special Create Parametric Filter Function.
Certain Smart APIs use the multi-tenant pattern that exposes a single entity set
at the service root, for example, Sites. In this case, you must navigate to the desired
entity set after selecting and loading the single entity set.
-
Select the Create Parametric Filter Function in the Navigator dialog box, in addition to any entity set(s) you wish to use.
Starting with the PBI Desktop August 2019 release, there is a bug that breaks parametric
filter support. If the Navigator dialog box displays with an error as shown, follow the sub-steps below to work around
the issue:
-
Click Transform Data from the bottom of the Navigator dialog box to invoke the Power Query Editor.
-
Select the Create Parametric Filter Function from the Queries panel.
-
On the Query Settings panel, under APPLIED STEPS, click X to delete Invoked FunctionData1.
-
Remain in Power Query Editor and continue with the steps below.
-
-
If the Power Query Editor is not displayed, click Transform data from the ribbon bar to load it. There will be items in the Queries panel for the selected entity set(s) and for the Create Parametric Filter Function.
-
Select the desired entity set from Queries. If necessary, navigate through to a table containing a column of filters.
You must know where the Smart API exposes parametric filters. They could be available
at the service root or as a navigation property relative to another entity set. See
the specific Smart API $metadata document or API documentation for details.
For example, the sample Smart API used here exposes Sites/Plants/Filters (that is, Filters is a navigation property from Plants). You can click Table under the Plants column header to navigate to plants from a site.
Some Smart APIs expose parametric filters as a navigation property named ParametricFilters,
vs. Filters.
-
Once you select or navigate to an entity that contains a Filters (or ParametricFilters) column, click the Add Column tab, and click Invoke Custom Function.
-
In the Invoke Custom Function dialog box, override the default New column name (if desired), for Function query, select Create Parametric Filter Function, and for filterSource, select the parametric filters property (Filters or ParametricFilters), as shown below.
-
Click OK, and the new column (for example, Eqp_Filter) is added to the end of the table.
-
Click Table under the Eqp_Filter column heading for the desired plant (row).
-
Click Function under the ExecuteParametricFilter column heading, and the filter dialog box displays, allowing you to enter the desired parameters and execute the query.
The documentation at the top provides information on valid parameter values. See
the specific Smart API documentation for more details on the supported parameters.
Based on the documentation, some parameter values are filled in as shown below.
-
Click OK to execute the query. The results are returned:
-
You can right-click the Invoked Function and select Advanced Editor to edit the input parameters to modify the results. Click Done to re-run the query.
