Calculated column to check if a value exists in another table

[swisdom]

I have two tables that are joined with a many-many relationship on Item Number.
One of them (Table1) is a reference table - it contains like 40 or so Item Number entries of a certain type (a few of which are duplicates with different other values associated, which is the only reason it is many-many)

The other (Table2) is a record of returns, with Item Number as one of the columns.

What I'd like to do is create a calculated column in Table2 which checks to see if that row's Item Number is represented in Table1.

Product Type X = 
if( 'Table2'[ITEM] IN related('Table1'[Item Number]), 1, 0 )

However, I get this error message: "The column 'Table1[Item Number]' either doesn't exist or doesn't have a relationship to any table available in the current context."
Despite the fact that they do have a relationship in the model

Any tips?

[Jeanxyz]

Related() only works in 1-to-many relationship, the calculated column has to be created on the many side of a relationship. That's why it won't work in your case. 

As I turnaround, I would create a new lookup table using table 3=values('Table1'[Item Number] ) and link it to table 2.  You can then write related formula 

Product Type X =
if( 'Table2'[ITEM] = related('Table3'[Item Number]), 1, 0 )

[bcdobbs]

Related won't work becuase it returns a single value. Due to the nature of your many to many relationship it could return multiple rows.

You need to count the rows using RELATEDTABLE. However you also need to pass the filter back to Table1 so in example that follows CROSSFILTER temporily lets filter go both ways.

Exists In Table 1 = 

VAR RelatedRows = 
    CALCULATE (
        COUNTROWS ( RELATEDTABLE(Table1) ),
        CROSSFILTER ( Table1[ID], Table2[ID], Both )
    )

RETURN IF ( RelatedRows > 0 , 1, 0 )

[bcdobbs]

Same idea but I prefer this version.

Exists In Table 1 = 
    CALCULATE (
        INT ( NOT ISEMPTY( ( Table1 ) ) ),
        TREATAS ( { Table2[ID] }, Table1[ID] )
    )

[swisdom]

I'm getting errors using both of those formulas.

[bcdobbs]

You just need to replace Table1[ID] and Table2[ID] with relevant table / column names from your model.

[swisdom]

Yes of course, but it doesn't find Table1 without the RELATED function.

[bcdobbs]

Have a look at my example. I think I've matched your model as far as I can see: Demo File 

If your table names have spaces make sure they're between ' ' Eg 'Table 1'[ITEM]

[swisdom]

I think this might be the problem.
DAX function "RELATED" does not work between DirectQuery and Import tables.
I have mixed sources.