Forum Discussion

Jruiz218's avatar
Jruiz218
Frequent Visitor
6 years ago
Solved

Help With Formula TRUE / FALSE / NULL

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!

Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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