Home Forums Power Pivot Can't get Find() to work in power pivot

This topic contains 1 reply, has 1 voice, and was last updated by  l_moseley 1 week, 1 day ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #7979

    l_moseley
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    I have a table in PowerPivot that is pulling a value from a related table using the related Function.  I’m then trying to test if that value is 1 of 4 values, and if so, return a value from another related table, otherwise return a value from another column within the same table.

    For example, I have a table with data from my accounting system with portfolio or company numbers named Accounting_tbl.  I have a crosswalk table that is linked that I use a RELATED() function to pull the related custodial bank account numbers (which happen to be alpha numeric).  So company number 100 in my Accounting_tbl is CUST19 at my custodial bank.  I want to then test all values in my table in the column with the RELATED() function to see if it is one of 4 values that I put in another table IntlFunds_tbl.  I attempted to do this using a formula found on one of Rob’s posts about SumX.  here is the formula I tried to use.  I get an error back in my Excel window saying “Cannot query supporting structures for column Accounting_tbl[CalculatedColumn1] because they depend on a column, relationship, or measure that is not processed.  Please refresh or recalculate the model.” But refreshing the model doesn’t clear the error.  Here is my formula:

    =IF(SUMX(IntlFunds_tbl,FIND(IntlFunds[Fund Number],Accounting_tbl[Acct_Fund_Num],1,0)>0,”Yes”,”Probably Not”)

    When I enter this formula I also get the following error “The end of the input was reached.  The calculated column contains a syntax error.  Provide a valid formula.”  I’m not actually trying to return Yes or Probably Not, I want to return a value from a linked table if the answer is greater than 0 and a column from the same Accounting_tbl if SUMX is 0.

    Can anyone tell me where I might be going wrong?

    #7984

    l_moseley
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Okay, after the original post I realized I left a ) before the >0 to close out the SUMX function.  I was able to get the formula to return the text values for true and false and then was even able to pull a value from the same table in the true or false result, but I have not ultimately been able to return a value from a linked table using the RELATED() function as the “true” argument of the IF statement.  Are there special rules to returning a value from a related table within an IF statement?  I should mention that the RELATED() function in another calculated column returns a value.  It’s only when embedded in the IF statement that it gives an error.  I also tried referring to another column in the same table, that itself was using the RELATED() function, but I get the same error about depending on a column, etc. that is not processed that I mentioned in the OP.

    Has anyone every successfully embedded a RELATED() function within an IF function?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.