Forum Discussion
Check if value is in another table - DAX
I have two tables with a one-to-many relationship on the child's id. The table has other columns including the columns I have depicted below. The two tables are:
| Child table | |
| child_id | location |
| 1 | Nigeria |
| 2 | Ghana |
| 3 | South Africa |
| 4 | Kenya |
| 5 | Zambia |
| 6 |
| parents table | |||
| parents_id | child_id | parent | location |
| 100 | 1 | father | Nigeria |
| 101 | 1 | mother | Togo |
| 103 | 2 | father | Kenya |
| 104 | 2 | mother | Kenya |
| 105 | 3 | father | Nigeria |
| 106 | 3 | mother | South Africa |
| 107 | 4 | father | Nigeria |
| 108 | 4 | mother | Nigeria |
| 109 | 5 | father | |
| 109 | 5 | mother |
I want to add a new column to the the child's table that returns YES if the child has the same location as either parents, NO if the location of the child is not the same as that of (either) the parents, BLANK if the child's location is blank. i.e I want to add the new column like the one below. How can I do this using DAX? Any help would be appreciated. Thank you so much.
| Child's table with added column | ||
| child_id | location | new column |
| 1 | Nigeria | yes |
| 2 | Ghana | no |
| 3 | South Africa | yes |
| 4 | Kenya | no |
| 5 | Zambia | no |
| 6 |
Try creating a new column like
new column = IF ( NOT ISBLANK ( 'child table'[location] ), IF ( 'child table'[location] IN SELECTCOLUMNS ( RELATEDTABLE ( 'parent table' ), 'parent table'[location] ), "Yes", "No" ) )
3 Replies
Try creating a new column like
new column = IF ( NOT ISBLANK ( 'child table'[location] ), IF ( 'child table'[location] IN SELECTCOLUMNS ( RELATEDTABLE ( 'parent table' ), 'parent table'[location] ), "Yes", "No" ) )Thank you so much. Before I posted my question, I have used the following dax query to create a new column. And it seem to be providing the same answer as your suggested solution. Just for learning purposes, am I correct?
DAX:
New column = IF(CONTAINS(RELATEDTABLE(parent_table), parent_table[location], child_table[location]),"yes", "no")
That's almost correct, the only slight issue is that you are not testing to see if the child location is blank. Other than that your solution is basically the same as mine, I think that IN is just another way of writing using CONTAINS that is just a little more readable.
