Dax function referencing another linked table

[Anonymous]

Hi Experts,

I have 2 tables, Table 1 and Table 2 with ID field used to link both tables. I will like to create a column in table 1 that outputs "1" if a column in Table 2 equals to a specific value if not then "0".

I am having issues using an if statement that references another table to filter out a value. Any help will be much appreciated.

Thank you 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a data sample:

Let's assume "Power BI" is the specific value, please try:

New = 
var _t=SUMMARIZE(FILTER('Table 2',[ID]=EARLIER('Table 1'[ID])),[Value])
RETURN IF("Power BI" IN _t,1,0)

Or use 

New 2 = 
var _t=SUMMARIZE( FILTER('Table 2',RELATED('Table 1'[ID]) && [Value]="Power BI"),[ID])
return IF([ID] in _t,1,0)

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Hi @Anonymous ,
By linked do you mean that there is an existing relationship between the two tables? On the one side of the relationship, you can use RELATED function in your formula. For example

FORMULA =
IF ( RELATED ( Table2[id] ) = "this value", 0, 1 )

[Anonymous]

Hi @danextian 
Table 2 column name does seems to appear. Yes by linked i mean there is an existing relationship (one to many) both direction