Forum Discussion
Data Mapping
I have 2 different datasets with bunch of records.
Table 1: Items, Item Received Quantity, Vendor
Table 2: Items, Item Defect Quantity, Vendor
Expectation is to get table (visualization) for Vendor, Item, Item Received Quantity, Item Defect Quantity
Question: How can I join two datasets to get the above result in one table? I'm trying to join by vendors and items but not getting correct results.
Any help on this will be appreciated!
Thanks
hi, Anonymous
For your requirement, you'd better create a data model as below:
Create two fact tables that a distinct list of vendor and a distinct list of item.
Then relationship like this:
Then when you create report, use Vendor and Item from Vendor table and Item table.
Best Regards,
Lin
5 Replies
The best way to do this is to create a 3rd table that is a distinct list of Vendors. Then create a 1 to many relationship between this table and tables 1 & 2. Then when you drag the Vendor name from this new table you should be able to drag the Item Received Quantity and Item Defect Quantity from the other 2 tables and it should "just work".
PS. If you do this I would also recommend hiding the "Vendor" column in tables 1 and 2 as using either of those will lead to the incorrect results you are currently seeing.
hi, Anonymous
For your requirement, you'd better create a data model as below:
Create two fact tables that a distinct list of vendor and a distinct list of item.
Then relationship like this:
Then when you create report, use Vendor and Item from Vendor table and Item table.
Best Regards,
Lin
> I tried this logic and seems working. However, I am not get failure rate from this relationship.
Have you made sure that the relationships are pointing in the same direction as the diagram posted by v-lili6-msft ? (so the arrows point from Vendor to the other tables.
And are you using the Vendor column from the Vendor table in your visuals? If you use the Vendor column from either of the other 2 tables you measure will not work as it will only slice the numerator or denominator.
