Way To Determine if Value in One PP Table Exists in Another PP Table

[CSMcVey]

I have two data sets (tables) in PowerPivot. One contains sales metrics with an account id column, we'll call this metricstbl. The other table contains all of the account ids in the system and the associated account information (location, phone #, etc.), we'll call this accountinfotbl. They are connected via a relationship.

What I would like to do is add a calculated column to the accountinfotbl that has either a 0 or 1. It would have a value of 0 if that row's accountid is NOT in the metricstbl and would have a value of 1 if that row's accountid IS in the metricstbl.

I'm sure I am just overlooking a simple function, any ideas?

Thanks for your time

 

[scottsen]

=RELATED(LookupTable[LookupColumn]) will return values from the lookup table (uhh... accountinfotbl in your case). So, something like...

=IF (ISBLANK(accountinfotbl[accountid]), 0, 1)