Problem: Consider the following Power BI report that shows the sum of ResellerSales[SalesAmount] by SalesTerritory[SalesTerritoryCountry], Employee[FullName], and ResellerSales[SalesOrderNumber].
This report is based on the following schema consisting of three tables.
However, If there isn’t a direct relationship between ResellerSales and Employee, the moment you add an unsummarized field from the second table on the many side, such as Employee[FullName] after adding SalesTerritoryCountry and ResellerSales[SalesOrderNumber), you’ll get the error “Error: Can’t determine relationships between the fields”.
Solution: Interestingly, the report works fine if a summarized field, such as COUNT(Employee[EmployeeKey]) is used. In this case, the SalesTerritory dimension acts as a conformed dimension joined to two fact tables. The reason why it doesn’t work when Employee[FullName] is added is because there is no aggregation on the Employee table and the relationship between ResellerSales[SalesOrderNumber] and Employee[FullName] becomes Many:Many over SalesTerritory which is now a bridge table. One employee may be associated with multiple sales and a sale can be associated with multiple employees. How do we solve this horrible problem? There are at least two ways:
- If possible, create a direct relationship between ResellerSales and Employee. Now you have two dimensions joining a fact table and there is no ambiguity.
- If the only join option is to relate Employee to SalesTerritory, set the relationship’s cross filter direction to Both. This will indirectly filter the ResellerSales table and might achieve the same result. For each employee on the report, Power BI travels the relationship to find the related territories at the lowest granularity. In Adventure Works, the lowest granularity of the SalesTerritory table is the territory region, such as South East, so the cross filter set to Both will select all regions that are associated to the current employee. From there, it will get the sales orders and sales associated with these regions.
Where the report using the second option differs from the first is if there are multiple employees associated with the same region causing the sales order numbers to repeat although the report total is the same.
Power BI supports flexible schemas but how you construct relationships may impact the report results. You should always start with a simple schema and grow in complexity if needed.
Which is the data source or does this matter? Is this a dynamic or static source? Also is the Sales Order Number a field unique only to Employee [FullName] (in other words are the Sales Order Numbers aggregated by Employee [FullName], are Order Numbers “owned” by single Employees or can several Employees use the same Number (apparently not here since only one Employee is attached to each Number). Final report sample shows duplication (?) since one Number attached to two different Employees? Does this not create an inaccurate total since the Order Number total SO61173 ($29,141) is counted twice. If this data source is SQL Server, when I create SQL Server tables, I need to make sure each table contains at least one unique field with Identity Specification (Is Identity) defined as Yes. Otherwise data diagram cannot be created in Management Studio and data relationship joins cannot be created. Also the system does not allow creation of Foreign Keys between tables? This is all based on use of SQL Server data sources. I do not know if the above holds true for other data sources (Teradata, MS Excel, Azure, etc.).
Hi Tom, I attached the sample to the blog to help you answer these questions. You’d need the forthcoming July release of PBI Desktop to open it. The total is unaffected because there PBI knows how to handle M:M relationships.