Self-Referencing Columns in Power BI

When working with big data, you will sometimes be in situations where you need to harness the capabilities of business intelligence but are inhibited by a lack of automatic hierarchy detection. Currently the relationship manager in Power BI does not directly support parent-child hierarchies for self-referencing tables. Because of this, you may often find yourself wondering how to get the system to recognize your desired relationship.

Luckily the formula language within Power BI, DAX, does have functions that allow the user to develop self-referencing columns within tables, which in turn allows for the creation of internal hierarchies. In today’s blog, we will consider how to use DAX to flesh out and visualize this difficult hierarchy information. We will discuss various strategies and their limitations.

Challenge:

Opportunity management is a typical subject that organizations would like to better understand with business intelligence. When data is queried and brought into Power BI, Relationships with records – such as Parent Accounts and Child Contacts – are typically understood and automatically set. In this case, we would like to consider the “Owner” field on Opportunity records in Dynamics. This field stems from a one-to-many relationship between the Systemuser entity and the Opportunity entity.

Consider a Sales Executive who would like to filter their report based on the Manager of Opportunity Owners. Additionally, the Executive would like to go up another level in the hierarchy (and filter on the Manager’s Manager). There are two ways that we can resolve this.

Solution 1: Lookups

The first is the use of only Lookup columns.

We will create our lookup columns within the Systemuser’s table. When pulling data from Dynamics 365, the Systemuser entity contains columns for systemuserid and _parentsystemuserid_value. Using these fields, we can create a self-referential lookup in DAX that will allow us to display the manager’s fullname.

Once we have created the Manager fullname column, we can then create the column to display the manager’s manager.

In this way we can successfully display multiple levels of an internal hierarchy in Power BI. But using a Lookup within a Lookup like this requires a good deal of processing power. It can slow down the system and increase wait times. Therefore, it should be used sparingly.

Solution 2: Index Column and Path Function

The second method involves the creation of an index column and the use of the path function.

We set an index column in the systemusers dataset in Query Editor.

And we rename the column.

From here we can create the calculated columns in the sysemusers table.

Using the following DAX:

MgrPathIndex = LOOKUPVALUE( systemusers[UserPathIndex], systemusers[systemuserid], systemusers[ManagerID] )

We create a numeric index column for users in this step for managers with the [systemuserid] and the [ManagerId]. These id fields are text GUIDs in CRM, so we need to convert them to numeric ID columns in order to use the PATH function in DAX.

Using this equation in DAX:

UserPath = PATH( systemusers[UserPathIndex], systemusers[MgrPathIndex] )

The PATH function is returning the user Index as well as the user’s managers indexes from the highest point of the hierarchy. If you are in the 3rd position of the hierarchy, you will receive a value in the column as “0 | 1 | 2” where 0 is level 1 ( the highest), 1 is the 2nd level, and 2 is the 3rd level. This method puts less stress on the system and is easier to use with a large hierarchy. It is the most efficient method and should be considered a best practice.

There is indeed a workaround or a solution for everything in Dynamics 365 and Microsoft Business Applications. Be sure to subscribe to our blog for more tips and tricks!

Happy Dynamics 365’ing

Restrict Customer Lookup to Accounts or Contacts in Dynamics CRM

Microsoft Dynamics CRM 2015 comes with customer lookup on entities like opportunity, case, order, invoice and quote out-of-the-box. This lookup resolves to account and contact by default, but sometimes you may have a need to show either accounts or contacts in this view. In earlier versions of CRM, you could achieve this by writing a script on the lookup control to define the default lookup type to 1 for account and 2 for contact, however, this method has been deprecated and a new method called addPreSearch has been added to the SDK. In today’s blog, we will explain how to use addPreSearch to show either accounts or contacts in the customer lookup. Let’s begin!

Blog Image

Example: You want to show only accounts on the customer lookup field on the order entity. Add the below java script library to the form and then add the defaultcustomer method to the onload event on the form.

function defaultcustomer(){

Xrm.Page.getControl("customerid").addPreSearch(addFilter);

}

 

function addFilter()

{

var customerAccountFilter = "<filter type='and'><condition attribute='contactid' operator='null' /></filter>";

Xrm.Page.getControl("customerid").addCustomFilter(customerAccountFilter, "contact");

}

Explanation: When you call the defaultcustomer, the system automatically attaches the addFilter method to the search criteria of the lookup control. In the filter condition of the addfilter method, we have mentioned that contactid equals null. contactid can only be null for accounts, as contactid is a primary key for contacts and cannot be null. If you want to display only contacts in the lookup then you can change the condition to be accountid equals null. After adding this script, the user will only be able to select an account in the customer lookup.

That’s all for the blog today, Remember to subscribe to our blog so that you can stay up to date on all the tips and tricks we post. And as always, happy CRM’ing!