Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
6 years ago
Solved

Not returning the value..

Hello,

I'm wondering if someone could help me with following..

 

I created new column in Data tab to return a value from another excel sheet, but the values are not returned...

No error is indicated.

 

Could anyone try to help me to understand what the issue could be?

 

Thank you!

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    It seems like coming from the same sheet not different. and You compared [FAM], which I do not see in this table.

     

    This how we get from another table

    New Column = sumx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])

     

    same table

    New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])

     

    Condition can change as per need

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    It seems like coming from the same sheet not different. and You compared [FAM], which I do not see in this table.

     

    This how we get from another table

    New Column = sumx(filter(table2,table2[Col1]= table1[col1] && table2[Col2]= table1[col2] ),table2[required_col])

     

    same table

    New Column = maxx(filter(table1,table1[Col1]= earlier(table1[col1]) && table1[Col1]= earlier(table1[col2]) ),table1[required_col])

     

    Condition can change as per need

     

    • KatkaS's avatar
      KatkaS
      Post Patron
      6 years ago

      Thank you, Amit!!! I had to add another condition for Period, but the logic you desribed worked very well.

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi KatkaS 

     

    Sure, can you explain what you would like to do, what is the relationship between these tables and if the table you are adding the column to is on one side of this relationship or on the many side?

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi KatkaS 

    try to use ALL() inside filter, like FILTER(ALL('2_Diff_Esc Data'), '2_Diff_Esc Data'[FAM Line]=...)

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    After my test, it works well here:

    Amount of ESC = CALCULATE(SUM(ECS[Amount]),FILTER(ECS,ECS[FAM]=EARLIER(BW[Operational Company])&&ECS[FAM LINE]=EARLIER(BW[FAM Line])&&ECS[Period]=BW[Period]))

    Here is my test pbix file:

    pbix 

    Please check it.

    Hope this helps.

     

    Best Regards,

    Giotto Zhi