Help with FIND() function in PowerPivot

[lmoseley7]

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 per the Crosswalk_tbl. I want to then test all values returned to the Accounting_tbl from the Crosswalk_tbl 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 Collie's posts about CONTAINSX. Below 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. I just thought returning the text would be a good proof of concept.

Can anyone tell me where I might be going wrong? I feel like I'm following the syntax of the post, but I didn't try to rebuild his example to make sure it worked since others that commented seemed to be able to use it. I tried simplifying the formula to just the FIND() function portion and it too gives me an error. I should mention that I use the auto-fill enter the names of the tables and fields so I know it's not an issue with misspelling or anything like that.

Any help would be much appreciated.

 

[Norie]

Could the problem be as simple as the missing closing parenthesis?

 

[lmoseley7]

Could the problem be as simple as the missing closing parenthesis?

I see I've written the example with a missing parenthesis but I don't think the actual formula is missing one because when I enter the parentheses, the last one highlights its pair which is right after the IF. The formula should read:

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

Unfortunately, I also changed the table names to make them more readable for you that might help and since the formula was in error I had to remove it to save the file. I will enter the formula again and ensure that a missing parenthesis was not the culprit.

Thanks for responding so quickly.

 

[lmoseley7]

Well, after re-entering the formula it now will return the "Yes" or "Probably Not". Now to see if I can return the fields that I wanted instead of the text. If there are any minefields I should be aware of, please let me know.

 

[lmoseley7]

Ok, so this is my actual formula at the current moment.

=IF(
SUMX(Intl_Fund_tbl,
FIND(Intl_Fund_tbl[Fund Number],
'Combined APS2 OBs'[Fund_CUSIP],1,0)
)>0,"Yes"
,'Combined APS2 OBs'[Scheduled Book Value])

It will return a Yes if it finds a match on the Intl_Fund_tbl and it will return the value from the same table in the [Scheduled Book Value] field. When I try to replace the "Yes" with RELATED(BBS_OBs_tbl[Local Total Cost Amount]) 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.”, which I was getting before like I mentioned in my OP. That same formula works to correctly return the related value in another column within this table and even referencing that column results in the error.

Here is the updated formula that creates the error message:

=IF(
SUMX(Intl_Fund_tbl,
FIND(Intl_Fund_tbl[Fund Number],
'Combined APS2 OBs'[Fund_CUSIP],1,0)
)>0,
RELATED(SSB_OBs_tbl[Local Total Cost Amount])
,'Combined APS2 OBs'[Scheduled Book Value])

 

[lmoseley7]

It appears that the issue stems from the IF statement trying to return a RELATED() value because I simplified the test to be:

=IF(4>2,RELATED(BBS_OBs_tbl[Local Total Cost Amount]),"Didn't Work")

and I still get the error. If I change the field to be a field within the same table I have no problems, but referring to a field in another table that is linked correctly, within an IF statement, won't work for me.

Suggestions?

 

[lmoseley7]

Ultimately I found my issue. At some point I had built two tables in Excel off the same query in Power Query. When I deleted one of the sheets with the table, it deleted the underlying query and with it when my connections. Once I realized my data was no longer connected and that I needed that extra ), everything started working a lot better. Live and learn I guess.