relationships

[mehaksultana_20]

Hi ,

I am creating a report based on 5 tables (customer,sales,date,location) 4 from sqlserver and 1 from excel.

All the three tables from sql are behaving as expected (filtering accurate data on date and region ) and working correctly with the relatinship, except for the table coming from excel. The excel have a column called Company Number which is named as customer id in other 2 sales and customer.

I have mapped excel.company number to sqlserver.customer.customer_id. 

still unable to achieve the relationship as when trying to create a visualization table campany name from excel  and total sales from sales error is popping. On selecting the option dont summarise on sales.totalsale the  error is popping asking to "FIX THIS".

The error is  "cant determine relationships between the two or more fields."

Kindly help , as how to establish the appropriate relationship in order to achieve the desired data.

[TomMartens]

Hey, not sure if I'm correct assuming your relationships looks similar to this

This works in the report, w/o throwing an error, the values shown are nevertheless wrong, another story.

If change the aggregation of amount to "Don't summarize", I got an error.

Assuming that your Excel files contains additional information that is not available in your SQL Server table and you just need the Excel table to gain access to this information (and there is a one to one relationship between sqlCustomer.customer_id and xlCustomer.customerid you can rebuild your relationship in this way.

Create a 1-to-1 relationship with filter direction "Both".

But, my final recommendation, is remove the 2nd customer table, and use the Merge function from PowerQuery to Pull the customername column from xlCustomer table into the sqlCustomer table. The simply reduces the number of tables and hence model complexity.

Hope this helps

Regards

Tom