Using Power BI to View Statuses in Dynamics 365

Leave a comment

In Dynamics 365, we can use Power BI to see statuses of records. Let’s go through an example of how to do this with Opportunities. Opportunity statuses include:

State Code: 0 = Open, 1 = Won, 2 = Lost
Status Code: 1 = In Progress, 2 = On Hold, 3 = Won, 4 = Canceled, 5 = Out-Sold

The Opportunity Close entity also has statuses:

State Code: 0 = Open, 1 = Completed, 2 = Canceled
Status Code: 1 = Open, 2 = Completed, 3 = Canceled

Let’s create a new Opportunity:

Let’s look at the state and status of this opportunity in Power BI. Open Power BI and enter the following blank query,

let
    Source = OData.Feed("https://yourorg.api.crm.dynamics.com/api/data/v9.1", null, [Implementation="2.0"]),
    opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(opportunities_table,{"statecode", "name", "statuscode", "opportunityid"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([name] = "My new opportunity"))
in
    #"Filtered Rows"

We see the state code is 0 (Open) and status code is 1 (In Progress):

Let’s also grab the OpportunityCloses entity with the query:

let
    Source = OData.Feed("https://yourorg.api.crm.dynamics.com/api/data/v9.1", null, [Implementation="2.0"]),
    opportunitycloses_table = Source{[Name="opportunitycloses",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(opportunitycloses_table,{"statecode", "statuscode", "_opportunityid_value", "opportunitystatecode", "opportunitystatuscode"})
in
    #"Removed Other Columns"

At this point, the Opportunity Id does not exist in the Opportunity Closes table.

Let’s change the status to “On Hold”:

Save the opportunity and refresh. The opportunity status code is now 2 (On Hold):

Now change it to Open for Bidding:

Change to Delivered:

At this point, it is not in the Opportunity Close table.

Let’s change back to In Progress, then close the opportunity. The 2 options are:

  • Purchased
  • Won

Let’s set to Won and click OK. Refresh Power BI to see the updated statuses. The State Code is 1 (Won) and the Status Code is 3 (Won):

At this point, the opportunity is in the Opportunity Close table.

Reopen the opportunity by clicking Reopen Opportunity from the toolbar:

Close the opportunity as Won. The state is now 1 (Won) and the status 100000004:

Now reopen and close as Lost. We see 2 status reasons – Canceled and Out-Sold:

2 = Lost, 4 = Canceled:

Each of the Opportunity Close records has a different timestamp, the latest one at the bottom shows State Code = 1 (Completed), Status Code = 2 (Completed):

Reopen, the close as Out-Sold. Now the status is 2 = Lost, 5 = Outsold:

And another close record, with 1 = Completed. 2 = Completed:

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.comLinkedIn Twitter | YouTube

 

Leave a Reply

Your email address will not be published. Required fields are marked *