Unable to Relate to another Table

[EZimmet]

Unable to Relate to another Table

I have a visual that shows fields from various tables but when exporting the data and underlying data the field from Table #3 does not Export.

If a table is directly connected to Table #1, I have no problem doing a Relate to a field.

In the below I need the Relate Field in Table #1 to pull from Table #3 for the data to export.

And the field EntID in tables 1 & 3 are not joined as it's different data if you were think just join them

Following message is received:  

The column 'Company[Name]' either doesn't exist or doesn't have a relationship to any table available in the current context.

[EZimmet]

Tried using both New Mesure and New Column with the following syntex:

Relate to Company - Name =
RELATED('Company'[Name])

The column 'Company[Name]' either doesn't exist or doesn't have a relationship to any table available in the current context.

Tables Asset to Tennant are related ------- Many to one(*:1) - Cross filter Direction -- Both
Tables Tennamt to Company are related -- Many to one(*:1) - Cross filter Direction -- Both

Other tables I have done as above if  directly connected to Table #1, no issue just this one that has to connect via another table having a issue with.

[v-jingzhang]

Hi @EZimmet 

From the screenshot of your first post, I found all three tables are of DirectQuery mode, so it is probably hitting the following two limitations of RELATED function and DirectQuery. 

DirectQuery limitation:

• A calculated column on an import table can refer to other tables, but a calculated column on a DirectQuery table can still refer only to columns on the same table. See a previous post (Related function not working).

RELATED function limitation:

• The RELATED function cannot be used to fetch a column across a limited relationship. A model relationship is limited when there's no guaranteed "one" side. It can be the case for two reasons:
  • The relationship uses a Many-to-many cardinality type (even if one or both columns contain unique values)
  • The relationship is cross source group (which can only ever be the case for Composite models)

Your Table #1 and Table #3 both have a one-to-many relationship with Table #2, does an EntID in Table #1 can always find a unique related EntID in Table #3? Will it match multiple values due to the many-side values in Table #2?

Best Regards,
Jing

[v-jingzhang · Accepted solution]

Hi @EZimmet 

Which operation were you doing when you saw the message "The column 'Company[Name]' either doesn't exist or ..."? Do you use the RELATED function? If so, it has some limitations. Some of them are:

• The RELATED function can only be used in calculated column expression.
• The RELATED function cannot be used to fetch a column across a limited relationship. 

And there are many considerations and limitations when exporting data from visuals. Check if your model and data hits any. Some of them are:

• Export using Underlying data won't work if you enable the Show items with no data option for the visualization Power BI is exporting.

• If the visualization uses data from more than one data table, and no active relationship exists for those tables in the data model, Power BI only exports data for the first table.

• Export using Underlying data won't work if:

  • the tables in the model don't have a unique key.

Please check above links first to troubleshoot this problem. If you still cannot fix it, let us know more details about the problem. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[EZimmet]

TY Sir 

You were spot on with the responce it also looks like using the underlying option caused the export error and the summary option worked.