Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.