In the first three parts of this Power BI series, we went through building a lean data set that minimized unnecessary database queries so working with our data remained fast and painless. At the point we left it in part three, it's totally usable as is, but we can make it even better by adding some formulas and custom tables.
Currently our data is spread across multiple tables, including the small custom tables we added to resolve optionsets. While this is perfectly functional, it's nice to have those optionsets as fields on the entity in question, so for example we can just Click a Status field on the opportunity table rather than having to scroll down through a potentially large number of optionset tables to find the one with opportunity statuses.
The old way of joining those tables in our queries and expanding the result achieved this, but at an unacceptable performance cost.
Fortunately we can handle this at the report level with a simple formula column.
To do this for Status on the Opportunity record, we'll right-click on Opportunities in our field list and select "New column."
Give the column a friendly name like Status (this is one reason why we left statecode as it was and did not rename it), then type "= LOOKUPVALUE(".
Power BI will prompt you for the three arguments.
Result_ColumnName is the value we want to display, in this case the Status column in the custom status table we built.
Search_ColumnName1 is the value in the target table we will be matching on.
Search_Value1 is the column in the table to which we're adding this custom column which will match Search_ColumnName1, in this case statecode in the opportunity table.
Our finished formula for Status looks like this:
We can now use the column we just created instead of the field in the optionset table:
Which not strictly necessary, I find this is easier in cases where me might have a dozen optionsets on an entity and much nicer to just have those dozen fields available as columns rather than scroll through a list of optionset tables.
The same principle can be applied to lookups, so for simple values like territory where I am only ever going to want a single column from that table, I bring those in as columns as well.
Once we have the data we need, we can right click on any field or table which is no longer needed and hide them:
This is the second reason for leaving fields that still need resolution with their schema names in the data model; you can see at a glance which fields still need attention at the report level.
In this case, we can hide statecode, territoryid, and the entire status and territory tables from our report - they're still accessible if you need them in the future, but hiding them make the report user-friendly for those who might not be intimately familiar with CRM's data structure.
At this point our reporting data is complete - all relationships have been built in, all optionsets and lookups resolved, and everything given a user-friendly name. We could stop there, but we can easily add another level of functionality by adding a custom date table.
As powerful as Power BI is, it doesn't have every feature we'd like right out of the box. One of the most important of those the ability to easily filter by date criteria - this month, last week, this fiscal year, the next twelve months, year to date, and so on.
The first step is to create custom date table. We'll go back into the query editor, right click in the queries area, and select Blank Query:
There are many ways to do this, but for simplicity we're going to borrow a complete table creation query (source), by clicking Advanced Editor and pasting the following into the query:
Once that's done we get a nice dialogue which lets us select a date range. We want to cover every date we might need for any data in CRM.
I chose 1/1/2010 to 12/31/2020 and let it generate the table.
The result is a table of every date, and every piece of the date we might want, for every day in that time span.
Some of these fields default to a generic data type, so we'll need to use the Data Type dropdown to change Year to a whole number and IsToday to a True/False.
Finally I will rename this table "Date Table" for easy identification, and close and apply my queries.
Back in the report editor, it's time for some more formulas.
First we'll create a measure for the current date by right clicking on the date table header, selecting New Measure, and then entering the "Today=DATE(year(now()),MONTH(NOW()), DAY(NOW()))".
Now we've have a reference point from which we can measure other dates by creating new columns with simple DAX formulas.
Here are just a few examples to indicate the possibilities:
Variations on the above will give us just about any date related criteria we want.
Before we can use those though, we have to relate the date table to our records.
To do that, we go back to Manage Relationships, add a new relationship, and define it like so for each date:
My relationship diagram now looks like this, with both accounts and opportunities tied to the date table.
Now I can replace the estimate close date on my opportunity table with the Date column from the date table and gain access to criteria such as the following:
This concludes our 4 part series on the basics of building high performance Power BI data sets and reports. If you haven't downloaded: 3 Best Practices to Improve Power BI Speed with Microsoft Dynamics 365 (formerly Dynamics CRM) white paper yet, do it now!
Interested in our Power BI services for your organization? Contact us or reach out to your account manager to learn more.