IF condition based on another table

[Anonymous]

Hi,

this is my scenario:

I have 2 tables:

TABLE_1
FK
calculated_column

TABLE2
FK
Boolean_field

My GOAL:
I need to show the content of the calculated_column of TABLE_1 only IF There is "1" in Boolean_field of TABLE2 else I want "N.A."

I tried (inside TABLE_1):
Column = IF(LOOKUPVALUE(TABLE2[Boolean_field]; TABLE2[FK];TABLE_1[FK]) = 1; TABLE_1[calculated_column]; "N.A.")

ERROR: Expressions that yield variant data-type cannot be used to define calculated columns.

How can I solve?

Thank you

[AlB · Accepted solution]

Hi @Anonymous

All rows in a column have to have the same data type. You seem to be assigning text and non-text in your IF.

What data type is TABLE_1[calculated_column]?

It it's a number, one way to solve this would be to substitute the "N.A." by a number that does not come up in the set of values you use and you can easily identify as equivalent to "N.A.". Or use blank() instead of "N.A.", as in:

Column =
IF (
    LOOKUPVALUE ( TABLE2[Boolean_field]; TABLE2[FK]; TABLE_1[FK] ) = 1;
    TABLE_1[calculated_column]
)