When we concluded the last blog in this series Importing Data for Speed Using Power BI with Microsoft Dynamics 365 for Customer Engagement, we had all our data query work done to bring in accounts and opportunities, but it wasn't yet reportable - optionsets and lookups remained unresolved, and we couldn't look at related data across tables.
The key to fixing this is relationships - we have to teach Power BI how our data is related.
In the past, I would have done joins at the query level to get all the data into a single table - e.g. customer account attributes on the opportunity - but that's a brute force method and completely unnecessary.
If you're following along, click on Manage Relationships in the main report editor. Power BI sometimes tries to guess relationships, but it's often wrong, since many optionsets share the same numeric values - there's no way for it to know if 1 means a status of won or a customer tier of 1. So if there are automatically generated entries, clear them out with the delete button and start clean.
Now I am going to teach Power BI how the data I brought in is related. I'll click new, and start with opportunity status - selecting the statecode field on opportunities, the status optionset table I created, and the value column to match on.
That's all it takes - one relationship learned.
Now I can remove the statecode column from my sample table by right clicking on the name and selecting Hide:
Now I can also remove that column from my report and add Status from the related optionset table:
Because we related the tables, Power BI now knows how to resolve the numeric values with the friendly name in the related status table, so we can put values from both tables in one chart. The results is the friendly status column we expect:
Because the tables are now related, we don't need to actually pull the data from one into the other to use it for reporting, selecting fields from different but related tables now works the same way as selecting them from a single joined table.
I can use exactly the same technique for relating entities, such as opportunity to account via the _customerid_value field. I'll repeat the same process as for statecode, selecting the opportunity and account entities and relating them through the _customerid_value and accoundid fields.
This allows me not just to display the account name of the opportunity, but report on opportunities using any field I have brought into accounts.
For example I can bring in address fields:
After adding territories, my relationships look as follows:
Our data set is now fully reportable, without the need to expand columns, join related tables, or any other performance killers.
Although fully functional in its current form, there are things we can do to make it even better. In the next blog of this series, we we'll build a custom date table and use formulas to extend our capabilities and polish the design.
Want more Power BI efficiency tips and best practices? Download this technical whitepaper: 3 Best Practices to Improve Power BI Speed with Microsoft Dynamics 365 (formerly Dynamics CRM).