Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax function referencing another linked table

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 val...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
danextian
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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