Is this person found on that table? DAX code help

[Anonymous]

Hi,

DAX newbie here.  Apologies if this is really simple, but my googling hasn't helped me get this right!

I'm attempting to create a calculated column in [Employee List] that returns the value "Yes" if Employee List[ID number] is also found in a linked table Promotions[ID number].  

Can anyone help me out - i keep getting an error message.

Thanks!

Heather 

[Anonymous]

// If the Promotions table is linked
// to the Employees table via the field
// [ID], this then should most likely 
// be a many-to-one connection because
// a person could be promoted many times.
// The structure therefore should be
// Employees 1 - * Promotion.
// If that's the case, it easy to create
// the calculated column.

[Ever Promoted] =
IF(
    ISEMPTY( RELATEDTABLE( Promotions ) )
    "No",
    "Yes"
)

Best

D

[lbendlin]

check = not isblank(related(promotions[idnumber]))

(assuming a M:1 relationship from employee to promotion. Otherwise check the lookupvalue() against blank() )

[Anonymous]

Thank you very much for responding.  Unfortuately it is not working, would this be because the promotions list isnt a full list like the full employee list?  It is only people who were promoted, so the ISBLANK isn't looking for matches where a value is blank, just the existence of a match...?

[lbendlin]

show your data model, show some sample data and the expected outcome please. 

[Anonymous]

Hi,

Here is an example of my tables.  Table 1 is a list of everyone.  Table 2 is a list of just some people.  The yellow is what I wish to insert, a calculated column on Table 1 if their employee ID is also found in table 2.   The two tables are linked through the employee ID.

Thanks.

[lbendlin · Accepted solution]

The relationship between the tables is actually 1:1 with bidirectional search .