Composite Models: Date Table incorrectly creates many to many relationships

[Anonymous]

With the Composite Models Preview feature enabled, we have connected to our data source with DirectQuery and generated a date table.  However, when trying to create a relationship between the date table and a data source table, it is being analyzed as many to many.  We double checked the date table and it was generated correctly (no repeating dates).

When trying to manually change the Cardinality, there is an error "The cardinality you selected isn't valid with this relationship." and will not allow the cardinality to be set.

We have recreated this issue in a separate Power BI instance connecting to a separate data source with the same outcome. 

With this being a preview feature, is it possible that this is simply a bug?

Appreciate the help!

[v-yuezhe-msft]

@Anonymous,

Do you create the Date table using DAX in Power BI Desktop? I can reproduce this issue only in the case that the Date table is created using DAX and the storage mode is mix in this situation.

When I connect to fact table and date table in SQL database using DirectQuery mode, relationship works as expected in Power BI Desktop.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft,

Hi Lydia!
Yes, the Date table was generated in DAX in Power BI Desktop.  We are connected to a RedShift data warehouse via DirectQuery (Using the Amazon Redshift connector) and are using the fact table from this source to set the date range of the generated Date table.  The storage modes are indeed mixed (DirectQuery on fact table, import for Date table).

Edit: We did some more testing regarding this issue.  

We brought in a dimension table from said RedShift data warehouse (DirectQuery), and creating a 1 to many relationship with the RedShift fact table works correctly. 

We also connected to a dimension table and another fact table in SQL Server (both import) to add to the model.
Creating a 1 to many relationship between these two tables also works correctly.

However, trying to create the relationship between the RedShift fact table (DIrectQuery) and the dimension table (import), we are experiencing the same 'Many to Many' error.  

[v-yuezhe-msft]

@Anonymous,

I have reported this issue internally: CRI 80367452. Will post back once I get any updates.

Regards,
Lydia

[Anonymous]

Thanks Lydia!

[v-yuezhe-msft]

@Anonymous,

I got response from PG:

"This is by Design. If you create a calculated table, it is going to be import always. By design, cross island relationship is always Many to many for now. For GA of composite model, we will allow a many to one cross island, but even that would still have some restrictions and wont support everything a same source many to one does."

Regards,
Lydia

[Anonymous]

I have the same issue on a non-calculated table. It is not a many-many relationship for sure. I also tried to import the same table (Azure SQL)- then it is a many to one relationship. Making the same relationship on the direct query table to the imported table- it is always suggesting a many-many. Is it by design even though it is not a calc table? The main issue using the many-many relationship is that it will not be supported before composite model feauture is GA. Hence we will not be able to test this approach.....