The most optimal way to specify the Partition Key filter (so that the aggregate functions can be pushed down to Cosmos DB) is to use dynamic M parameters. To use dynamic M parameters, you would create a data set with unique Partition Key values, create a parameter, add it as a filter on the main data set, bind it to the unique Partition key data set, and use it as a slicer for the main data set. Use the following steps to enable dynamic M parameters for Partition Key filtering.
a. Create a data set with unique partition key values:
In Navigator, select Transform Data instead of Load to bring up the Power Query editor. Right-click on the queries data set, and then select Duplicate to create a new data set.
Rename the new Partition Key model, then right-click on the Cosmos DB partition key column. In this example, Product is the Cosmos DB partition key column. Select Remove Other Columns, and then select Remove Duplicates.
b. Create a parameter for dynamic filtering:
In the Power Query editor, select Manage Parameters > New Parameter. Rename the new parameter to reflect the filter parameter and input a valid value as Current Value.
c. Apply parameterized filter on the main data set:
Select the dropdown icon of the Partition Key column, then select Text Filters > Equals. Change the filter type from Text to Parameter. Then choose the parameter that was created in step b. Select Close & Apply on top left corner of the Power Query editor.
d. Create Partition Key values slicer with parameter binding:
In Power BI, select the Model tab. Then select the Partition Key field. From the Properties pane, select Advanced > Bind to parameter. Choose the parameter that was created in step b.
Select the Report tab and add a slicer with the unique Partition Key.
e. Add visualizations and apply Partition Key filter from the slicer:
Since the chosen partition key value on the slicer is bound to the parameter (as done in step d) and the parameterized filter is applied on the main data set (as done in step c), the chosen partition key value is applied as a filter on the main data set and the query with the partition key filter is passed down to Cosmos DB in all visualizations.