Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-lili6-msft
Community Support
Community Support

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:

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.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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:

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.
Anonymous
Not applicable

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?

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

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.

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
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.