Recently when configuring a Power BI Data Model for Dynamics 365 I encountered a situation where I was not permitted to create a One-to-Many relationship in the data model between a Dimension table and a Fact table. Power BI automatically created a Many-to-Many relationship between the tables concerned and would not allow this relationship to be recreated or reconfigured as a One-to-Many relationship.
Here are the two tables, Supporter and Material Requests showing the relationship created by Power BI. The relationship is between CustomerKey in the Supporter table and CustomerKey in the Material Requests table. If retained, the bidirectional many-to-many relationship can introduce unwanted side effects into the use of the data model with respect to calculations and filtering. At best, I was only able to configure the Filtering Direction on the relationship to be Single rather than Both so that Supporter filtered Material Requests and not vice versa.
In this data model, Supporter is a Dimension table and Material Requests is a Fact table. The Supporter table is loaded into the data model as a result of Power Query merging the Dynamics 365 Account and Contact tables. The Account and Contact tables themselves are not loaded into the data model.
The Material Requests table is a custom Dynamics 365 table related to Accounts and Contacts via a parent CustomerId. In the data model, this field is renamed to CustomerKey.
CustomerKey in the Supporter table is a Calculated Column generated using the following DAX formula:
The formula refers to a Custom Column, added by Power Query when processing the source Accounts and Contacts, named Record Type. Record Type is used to identify whether the Supporter is an Organisation or an Individual. The CustomerKey formula, sets the CustomerKey from either the AccountKey or the ContactKey on the Supporter. These are the renamed Dynamics 365 accountid and contactid fields.
In this example, it was expected that a One-to-Many relationship should be able to be created from Supporter to Material Requests because the CustomerKey values in the Supporter table are unique. I temporarily added a Calculated Column to the Supporter table to test the uniqueness of each row using the following DAX formula:
The presence of CALCULATE in this formula invokes a Context Transition from a Row Context to a Filter Context, therefore COUNTROWS is subsequently executed against Supporters filtered by the all the columns of the current Supporter row being processed. This calculation confirmed that there was only one unique row for each Supporter and therefore implied with a high degree of certainty that CustomerKey was also unique. Note: If CustomerKey was configured as a Primary Key in the data model then the Context Transition would filter by CustomerKey instead of by all the columns in the row.
I then realised that the issue would be related to the presence of blank CustomerKey values in the Material Requests table. After checking for this, I found that this was indeed the case. This indicated bad data because all the Material Requests should be related to a parent Customer (i.e. Account or Contact); there should not be any blank CustomerKey values.
The resolution was then straight forward. I simply updated the Power BI Query used to load the Material Requests to exclude those Material Requests with a blank CustomerKey as shown here.
As a result, I was then able to update the relationship from the Supporter to the Material Requests table so that it was a One-to-Many relationship configured so that Supporter filters Material Requests as shown here: