Forum Discussion

yaya1974's avatar
yaya1974
Icon for Helper III rankHelper III
2 years ago
Solved

Looking up values from another table with multiple filters

Hi.   

Cust CL8-OH Share = CALCULATE(FIRSTNONBLANK(TruckBuildShare[Class 8 - OH Cust],1),FILTER(TruckBuildShare,TruckBuildShare[Customer]=BumperOEM[Customer]),FILTER(TruckBuildShare,TruckBuildShare[Year]=BumperOEM[Year]))
 
This is my formula, I thought it was working great.  Then today I finally have all my data and realized it is only pulling in the first value of the first row from other table (52.8%).   What function should I use instead of FIRSTNONBLANK to bring me back all values?  
 

As you can see the value changes every month.  So I don't need the first value, I need my formula to pull in all the values.

Please this is pretty urgent.  Appreciate all the help!    I will continue to figure it out, if I do, I will let you all know.

Thank you!!!   

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi yaya1974 ,

     

    What does "bring back all values" mean? Could you please provide an example?

     

    If the value in the TruckBuildShare table is different for each month, and you want to use Cust CL8-OH Share to get the value for each month. Then you should modify your filter so that the filter is accurate to month.

    Cust CL8-OH Share = CALCULATE(FIRSTNONBLANK(TruckBuildShare[Class 8 - OH Cust],1),TruckBuildShare[Customer]=BumperOEM[Customer],TruckBuildShare[Year]=BumperOEM[Year]，TruckBuildShare[Month]=BumperOEM[Month])

     

    If you want to get the sum of each month instead of just returning the first value of each month, you can use SUM.

    Cust CL8-OH Share = CALCULATE(SUM(TruckBuildShare[Class 8 - OH Cust]),TruckBuildShare[Customer]=BumperOEM[Customer],TruckBuildShare[Year]=BumperOEM[Year]，TruckBuildShare[Month]=BumperOEM[Month])

     

     

    Best regards,

    Mengmeng Li

4 Replies

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

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.  Do you want a masure or a calculated column formula solution?  What do you mean by bring back all values?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi yaya1974 ,

     

    What does "bring back all values" mean? Could you please provide an example?

     

    If the value in the TruckBuildShare table is different for each month, and you want to use Cust CL8-OH Share to get the value for each month. Then you should modify your filter so that the filter is accurate to month.

    Cust CL8-OH Share = CALCULATE(FIRSTNONBLANK(TruckBuildShare[Class 8 - OH Cust],1),TruckBuildShare[Customer]=BumperOEM[Customer],TruckBuildShare[Year]=BumperOEM[Year]，TruckBuildShare[Month]=BumperOEM[Month])

     

    If you want to get the sum of each month instead of just returning the first value of each month, you can use SUM.

    Cust CL8-OH Share = CALCULATE(SUM(TruckBuildShare[Class 8 - OH Cust]),TruckBuildShare[Customer]=BumperOEM[Customer],TruckBuildShare[Year]=BumperOEM[Year]，TruckBuildShare[Month]=BumperOEM[Month])

     

     

    Best regards,

    Mengmeng Li