Forum Discussion
Fact Table Values Showing Blank When Including Dimension Field
I have a 'Sales' as a fact table in my model. I have another two dimensions which assigns salespeople to invoices based on a compound key 'CustomerAssigmentsID' comprised of [Branch], [Date], and [CustomerKey]. 'Customer Assignments' relates directly to 'Sales' with the compound key. The model then relates 'Outside Salespeople' to 'Customer Assignments' to show the salesperson's information. Everything is as expected when showing 'Sales' fields such as [Profit] and 'Customer Assignments' field [OutsideSalespersonID]. However, if I add fields from 'Outside Salespeople' then 'Sales' fields such as [Profit] will show blank for rows that do not have a salesperson (counter sales do not have an outside salesperson). This doesn't change the total row values.
Relevant Relationships:
Sales[CustomerAssignmentsID] *:1 Customer Assignments[CustomerAssignmentsID]
Customer Assignments[OutsideSalespersonID] *:* Outside Salespeople[OSSid] (Outside Salespeople filters Customer Assignments)
Current Results:
Expected Results:
Model:
Hi B_Rax ,
I understand what you're saying, and I know it's not an oversight on your part that's causing the table to show up blank with no data in it.
But what I mean is that when it's blank, they don't create a direct connection between them, and in many-to-many it creates a misconnection internally, making the final result incorrect.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi B_Rax ,
Since you're using a compound key, ensure that the granularity of your 'Sales' table matches that of the 'Customer Assignments' table. Mismatches in granularity could lead to unexpected blanks in your visuals. You can read this document for a further study: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Also I think you can create a DAX measure in your 'Sales' table that handles cases where there is no corresponding 'Outside Salesperson'. This measure can replace blanks with a default value or perform calculations that account for the absence of an outside salesperson.
Profit with Default = IF(ISBLANK(SUM('Sales'[Profit])), 0, SUM('Sales'[Profit]))This measure will replace blank 'Profit' values with 0, ensuring that your visuals do not show blank values unexpectedly.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the response. As far as I can tell the granularity is the same between Sales and Customer Assignments. Their relationship works fine as long as the relationship remains 1:* as it breaks when *:*. I created a test Salesperson table which was 1:* with Customer Assignments instead of *:* and that returns expected results. I'm not sure why the *:* relationships with mono-directional filtering do not work even when aggregated as Min/Max.
Regarding the DAX, I attempted to implement it but the default value causes filtered records to appear.Hi B_Rax ,
Applying many-to-many relationships in Power BI is prone to errors. It's easy to create aggregation or some other unwanted error, so if you can provide me with more information about your problem or .pbix file, I think I can solve the problem more intuitively.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
