Power BI Microsoft Dynamics CRM Online – Power View Report Part 3

Colin Maitland, 14 July 2015

This is the third blog in a series of three blogs on how to create a simple Power View Report in Microsoft Excel to display information on Accounts and Opportunities retrieved from a Microsoft Dynamics CRM Online Organisation. The report is based on an ODataFeed related query created using Power Query and named AccountOpportunitySet. The creation of this query was demonstrated in previous blogs in this series.

In my previous blog I described how to add and format the Chart displayed at the bottom of this report. In this blog I will describe how to add filters to this report so that either the Table or the Chart or the Report can be filtered by Account, Address State, Opportunity Owner, Opportunity Status and Opportunity Status Reason.

Complete the following steps:

1. If required, click the Filters Area button on the POWER VIEW tab of the ribbon bar to display the Filters Area:
   

   

2. If required, click the Field List button on the POWER VIEW tab of the ribbon bar to display the Power View Fields pane.

   

When the Filters Area is displayed it may display a tab named TABLE or a tab named CHART in addition to the tab named VIEW based on whether or not the Table or the Chart in the report are currently selected.

The TABLE tab is displayed whenever the table is selected and by default already contains the fields included in the Table. These fields may be used to filter the Table by the selected values:

The CHART tab is displayed whenever the Chart is selected and by default already contains the fields included in the Chart. These fields may be used to filter the Chart by the selected values.

The VIEW tab will by default not contain any fields.

Additional filter fields may be added to any one of these tabs including fields that are not displayed in the Table or the Chart such as Address State. The following steps describe how to add the Account, Address State, Opportunity Owner, Opportunity Status and Opportunity Status Reason fields to the VIEW tab how to add the Address State field to the TABLE and CHART so that either the Report the Table or the Chart can be filtered by Address State.

1. Select the VIEW tab in the Filters Area.
2. Drag-and-drop the Account, Address State, Opportunity Owner, Opportunity Status and Opportunity Status Reason or equivalent fields from the Power View Fields area into the VIEW tab in the Filters Area.

        

1. If required, select the Table on the report so that the TABLE tab is displayed in the Filters Area.
2. Select the TABLE tab in the Filters Area.
3. Drag-and-drop the Address State or equivalent field from the Power View Fields area into the TABLE tab in the Filters Area.

1. If required, select the Chart on the report so that the CHART tab is displayed in the Filters Area.
2. Select the CHART tab in the Filters Area.
3. Drag-and-drop the Address State or equivalent field from the Power View Fields area into the TABLE tab in the Filters Area.

The following image shows both the Table and the Chart in the report being filtered together using the filter controls on the VIEW tab of the Filters Area to display records related to Accounts where the Address State is TX. In this example only the Won Opportunities are displayed in the Table based on the fact that the Won tab, rather than the Open tab, of the Table is selected.

The following image shows only the Table in the report being filtered using the filter controls on the TABLE tab of the Filters Area to display records related to Accounts where the Address State is TX. All Accounts are still displayed in the Chart.

The following image shows only the Chart in the report being filtered using the filter controls on the CHART tab of the Filters Area to display records related to Accounts where the Address State is TX. All Accounts are still displayed in the Table.

In my next blog I will demonstrate how to create a Power Map Report in Microsoft Excel based on the AccountOpportunitySet query which retrieves the underlying Account and Opportunity data from a Microsoft Dynamics CRM Online Organisation.