Data Mapping

[Anonymous]

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

[v-lili6-msft · Accepted solution]

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

[Anonymous]

Hi, Thanks for looking into this. This is really cool 🙂

I tried this logic and seems working. However, I am not get failure rate from this relationship.

Failure Rate = item defect qty / item received qty

Can you please help me?

[v-lili6-msft]

hi, @Anonymous 

This measure should be this:

Failure Rate = SUM( Table2 [item defect qty]) /SUM( Table1 [item received qty])

Then when you create report, use Vendor and Item from Vendor table and Item table.

If it is not your case, please share your simple pbix file and expected output . You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[d_gosbell]

> 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.

[d_gosbell]

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.