Forum Discussion
Missing values in Tables
I don't think I understand how the Table visual works in Power BI. Have these three tables. Customer is the main table and the Sales and Telephone calls tables hang off it. Notice not every customer has made a phone call.
Here's my model
I've also created a DAX measure to sum the sales value
Looks okay. Now I add telephone call date.
You can see it's giving me every combination of telephone date and customer. So I updated the model to make the customer to telephone call table link work both ways.
Now I get this
Looks okay, but I'm missing the customers that didn't make calls. So I change the table to show items with no data. And I get...
So the non-calling customers are now showing, but now the DAX measure is not showing their sales value, but the total is the sum of the whole sales table. Is there a way to get the DAX measure to show on every row? If I remove call date from the table, the DAX measures come back.
Is there something I'm not understanding? Should I only use columns from one table in a table visualisation? Do I need to redesign my model?
Anyone got any ideas?
Thanks.
I've observed that the logic functions correctly when the relationship between the Telephone Calls and Customer tables is one-to-one. Since there are no duplicate customers in the Telephone Calls table, Power BI assumes that each customer makes only one call, leading to the desired result.
However, when there is a cardinality between the tables—specifically, since Customer ID serves as the primary key connecting both the Telephone Calls and Sales tables—if you include fields from all three tables in a table visual, Power BI requires that all customer IDs be present in each table.
One potential solution is to merge the tables using a left join with the Customer table in Power Query, then group by Customer ID and set the aggregation to SUM(Sales).
3 Replies
I've observed that the logic functions correctly when the relationship between the Telephone Calls and Customer tables is one-to-one. Since there are no duplicate customers in the Telephone Calls table, Power BI assumes that each customer makes only one call, leading to the desired result.
However, when there is a cardinality between the tables—specifically, since Customer ID serves as the primary key connecting both the Telephone Calls and Sales tables—if you include fields from all three tables in a table visual, Power BI requires that all customer IDs be present in each table.
One potential solution is to merge the tables using a left join with the Customer table in Power Query, then group by Customer ID and set the aggregation to SUM(Sales).
Hi Newcolator
Agree with MaheshkumarSuma's solution.
Here's a little something I'd like to add:
If you want to keep the relationship between the “Customers” table and the “Telephone Calls” table in your model as a one-to-many bi-directional relationship, you can create a measure to control for customers who have not made a call to show a blank in the call date column. The DAX as follows:
Call Date Display = IF( ISBLANK(SELECTEDVALUE('Telephone calls'[Call date])), BLANK(), SELECTEDVALUE('Telephone calls'[Call date]) )However, this solution only works for one call date per customer. So it is necessary to clarify the correspondence between sales value and call date, it is suggested to consider establishing a relationship between the "Telephone Calls" table and the "Sales" table through the date column in the model.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Very helpful. Thanks both.
