Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Ve...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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?

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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