Help With Formula TRUE / FALSE / NULL

[Jruiz218]

Hello Everyone,

Im new with DAX and im looking help to build a formula for the following scenario, PowerPivot data model with two tables:

Table A has the following fields:
UniqueID
Date
Team 

Table B has the following fields:
UniqueID
Date

I have both tables connected by the unique ID column, and what i need to validate is the following:

In table A, confirm if uniqueID exist on both tables, if not NULL, if it exist, confirm if it was assigned to Team X, if not, FALSE, and if BOTH conditions exist, then TRUE.

It doesnt have to return TRUE / FALSE / NULL, i just need to be able to identify the 3 scenarios.

Any help will be highly appreciated!

[mahoneypat · Accepted solution]

Please try this expression for your calculated column on the TeamA table.  Note that I had a 1:1 relationship between them, so you will have to adapt this to RELATEDTABLE() if TeamB is on the many side.

Result =
VAR existsB =
NOT ( ISBLANK ( RELATED ( TableB[UniqueID] ) ) )
VAR teamX = TableA[Team] = "X"
RETURN
SWITCH ( TRUE (), AND ( existsB, teamx ), "TRUE", existsB, "FALSE", "NULL" )

Result =
VAR existsB =
NOT ( ISBLANK ( RELATED ( TableB[UniqueID] ) ) )
VAR teamX = TableA[Team] = "X"
RETURN
SWITCH ( TRUE (), AND ( existsB, teamx ), "TRUE", existsB, "FALSE", "NULL" )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Jruiz218]

Thank you very much @mahoneypat  it worked just fine!