Forum Discussion
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 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
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.
3 Replies
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 exampleFORMULA = IF ( RELATED ( Table2[id] ) = "this value", 0, 1 )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
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.
