Create a flag from lookup of data in another table

[ryangleason]

I have two tables - Table A has a User ID column.  Table B has two Columns - User ID and Subscriptions.  This is a one to many relationship, joined by User ID.  What I am trying to do is add a column to Table A that would return Yes/No if a certain Subscription is found in Table B.  The value in Subscription I want to search for is "Ambassador".

I'm new to PowerBI so apologizes and the searches I have been doing don't quite fit the simple scenario I am trying to do with DAX.

[lbendlin · Accepted solution]

The ideal solution would be not to have to do that at all, but to let the data model do the work for you.

Second best is to use TREATAS to project filters from one table to another even if they are not directly connected

And lastly, shamefully, there is LOOKUPVALUE.

[Anonymous · Accepted solution]

Hi @ryangleason ,

As @lbendlin suggested, you can get the value of the field from another table by using LOOKUPVALUE function.

You can also create a calculated column in table A as below to get it:

Column =
VAR _userid = 'A'[User ID]
VAR _subscription =
    CALCULATE (
        MAX ( 'B'[Subscriptions] ),
        FILTER ( 'B', 'B'[User Id] = _userid && 'B'[Subscriptions] = "Ambassador" )
    )
RETURN
    IF ( NOT ( ISBLANK ( _subscription ) ), "Yes", "No" )

Best Regards

[lbendlin · Accepted solution]

The ideal solution would be not to have to do that at all, but to let the data model do the work for you.

Second best is to use TREATAS to project filters from one table to another even if they are not directly connected

And lastly, shamefully, there is LOOKUPVALUE.