Relationships between tables numerical vs categorical

[pxg638]

Hi there,

I am struggling to get my head around this, and a quick search didn't show this up anywhere else, but apologies if already solved. I have a simple 3 table data model: header table holds each object once, with a one to many relationship from there to two other tables. 

Table 1 has each named object once

Table 2 has some of the objects and with that a column telling me location (1:* relationship with table 1)

Table 3 has some of the objects, sometimes multiple times, with a column telling me options on that object  (1:* relationship with table 1)

I am looking to create a simple table visualisation showing properties from those two child tables, namely:

ObjectName

ObjectLocation

ObjectOptions

Where ObjectName and Options come from table 3, and ObjectLocation comes from table 2. 

Doesn't work however, says it cannot determine the relationship between two or more fields. But, and this is the weird part, if rather than using ObjectOptions (a string data type) I build this using a numerical property, it works absolutely fine, 

ObjectName

ObjectLocation

ObjectPartCount

Works fine, and I can then add:

ObjectName

ObjectLocation

ObjectPartCount

ObjectOptions

 It is as if it can handle the relationship if it is numerical, but not categorical. Has stumped a few people here, and we don't want to be creating reports with superfluous values in just to make this work.....so what have we got wrong in our understanding/execution of this?

[v-jiascu-msft · Accepted solution]

Hi @pxg638,

Sorry, I missed your message. Let's see a more vivid example. The explanation will be more clear.

Table DGSRouting (* many side):

well_name column1 column2

Jack           Baby1      10 

Jack           Baby2      20

Table Hopper (* many side):

Well Name   column1       column2

Jack               Basketball    100

Jack               Swiming       200

Table WellHeader (1 1-side):

Name   column1

Jack      Male

1. If we bring the columns from DGSRouting and Hopper into a table visual, it's obvious the relationship can't be determined.

Baby1   Basketball (? Swiming?)

Baby2   Basketball (? Swiming?)

2. Why numerical columns work? Because we can aggregate the numerical data though there could be no senses.

Baby1 300

Baby2 300

3. There is still a workaround for those columns that is exactly in the relationship columns. Because they are the same, we need only one value.

If you have any questions, please feel free to post here.

Best Regards,

Dale

[v-jiascu-msft]

Hi @pxg638,

The field ObjectName should be from the Table 1, which is usually the function of the 1 side of the relationship. Can you please share a sample? Or I can't explain why the numerical field could work. You can mask the confidential part first.

Best Regards,

Dale