I raised the issue last week on the forum and I incorporated suggestions mentioned in the solution. However, this time I came across the same issue and tried to followed the guidelines given in the solution. It did not help this time. Following is the my data model:
I am trying to show the columns from different tables in table visual. I am trying to show contactId (from contact table), AssetId (from Asset table), AddressId and Formatted Address (from Address table).
Table visual displays column values until I dragged AddressId or FormattedAddress from Address Table. When I tried to display any column from Address or AssetAddress table, the visual throws an error about ‘can’t determine the relationship between two or more fields’.
I made sure that the AssetAddress table has no missing values. I also tried to change the cross relationship direction from single to both but same result. Moreover, I don’t think creating a bridging table between Asset & AssetAddress tables would retain the unique no of records in the Asset table (because of 1:M relationship between two tables). So, I did not create the bridging table.
@leo_89 Columns from Asset and Contact work fine because they are joined by Asset Contact table, columns from Address and AssetAddress do not share a common table with Contact that’s why you get the error, it is nothing to do with the relationships already existing it is more about the relationships that do not exists/can’t be created and internal optimization that the DAX Engines have to do.
Let’s say if Contact table has a column with 10K unique rows and Asset table also has a column with 10K unique values and you use both columns in a report, if the engine was naive it would create 100 Million combinations every time the query is refreshed (This CROSSJOIN operation is handled by only 1 CPU core). But there is a possibility that AssetContact only has 500 unique Assets and 1000 unique contacts, in that case the engine will report 500K records which is easier to manage than 100 Million, that’s almost 7-9 orders of magnitude of difference.
In order to reduce those 100 Million combinations to minimum the engine needs a bridge table to identify only the existing combinations which it can do by simply performing a COUNTROWS on AssetContact_V
So this is a data modelling issue rather than PBI problem. Merge Asset Address into AssetContact table and you can eliminate this issue completely, this is why the experts recommend using Star or Snowflake schema, the moment you divert from this you will have issues.
