Related tables - not sure what I'm doing wrong

Deeks — Tue, 13 Feb 2024 23:23:29 GMT

Hi, I have three tables linked to each other. Let's call them table A, B, C

I am trying to check whether an order number in A, exists in B and C. I would have liked to create two columns in table A; one would show whether that order no. is within B and the second column to show existence in C. However, I am unable to do this, when using the RELATE function.

I am able to do this separately, within table B and C. But this does not help when trying to show the information in report view. It seems like the relationships are incorrectly setup.

The relationships that are setup are

Table B to A (many to one)
Table C to A (many to one)

I have tried to setup the following

Table A to B (one to many)

Table A to C (one to many)

but this is automatically reverted to the former relationships.

FYI - I am not very good with relationships and cardinality so please bare that in mind when responding, thank you! 🙂

Re: Related tables - not sure what I'm doing wrong

edhans — Wed, 14 Feb 2024 01:22:23 GMT

You can do this with the CONTAINS() function. If you are adding a column, you could use this as a Calculated Column:

CONTAINS ( store, Store[CountryRegion], ThisTable[CountryRegion])

This would look up the CountryRegion field in this table and see if it exists in the Store table in the CountryRegion field there and return TRUE or FALSE.

topic Related tables - not sure what I'm doing wrong in DAX Commands and Tips

Related tables - not sure what I'm doing wrong

Re: Related tables - not sure what I'm doing wrong