cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chinmay_1911 Regular Visitor
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Data Mapping

hi, @chinmay_1911 

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:

1.JPG

 

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
d_gosbell Senior Member
Senior Member

Re: Data Mapping

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.

Community Support Team
Community Support Team

Re: Data Mapping

hi, @chinmay_1911 

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:

1.JPG

 

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

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chinmay_1911 Regular Visitor
Regular Visitor

Re: Data Mapping

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

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?

d_gosbell Senior Member
Senior Member

Re: Data Mapping

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.

Community Support Team
Community Support Team

Re: Data Mapping

hi, @chinmay_1911 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.