Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Multiply two values based on conditions from multiple tables

Hello,   I have three tables:   Table 1 - Materials per product   ProductID Material Weight 1 Cotton 50 2 Polyester 30 2 Cotton 10   Table 2 - Products   ProductID...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

In the MPP table, write these calculated column formulas

Fabric type = RELATED(Products[FabricType])
Factor = LOOKUPVALUE(CF[Factor],CF[FabricType],MPP[Fabric Type],CF[Material],MPP[Material])

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Ashish,

     

    Thanks for your help!

     

    If I do this, then it returns an #ERROR, stating "A table with multiple values was given while a singular value was expected" (translated). 

     

    What should i do differently?

     

    Thanks again.

    • Ethan96's avatar
      Ethan96
      Icon for Resolver I rankResolver I
      4 years ago

      Hi， Anonymous 

      I don't see obvious problems in the formula, I doubt it's a problem with the data.

      Please check the following points

      1. Ids in 'Table 2'[ProductID] are unique

      2. No invalid data rows (null values) in your original model

      3. There is only one corresponding factor value for each material and fabric type in ‘Table 3’

       

      Regards,

      Ethan

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      This error means that in the CF column, there are duplicats appearing in the Fabric type and/or Material columns.  Please share a representative dataset and on that one, show your expected result so that i can share the appropriate formula.