Relationship Issue

[lockartpt]

Hi,

I have 2 tables (Table 1 with "Period start time" and table 2 with exact the same column name)

The dates from Table 1 start from 1-10-2021 00:00 (dd-mm-yyyy hh:mm) to 14-10-2021 12:00. The dates from Table 2 start from 8-10-2021 00:00 to 14-10-2021 23:00.

When I load the tables into power BI the 1st thing that I notice is that no relationship is detected (i'm not sure why) Is this due to the fact that both columns dont start and end with the same dates?

Next I manulally create a relation between these 2 columns (many to many on both directions). when I put both of the columns in a table element something strange happens:

Both of them have the "show items with no data active". And both of them are Date/Time format. What I found strange is that the values from the 2nd column should only start to appear when the 1st column reaches 08-10-2021 and that does not happen. Should't powerBI be able to correctly compare both date/time values?

Do you have any ideia why this is happening?

[v-yangliu-msft]

Hi  @lockartpt ,

I created the data, but your scenario cannot be reproduced. The data in the two columns will be displayed with the correct date.

Or you try to set the relationship as one-to-one.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens · Accepted solution]

Hey @lockartpt ,

maybe the Auto-detect option is turned off, check your settings:

A relationship of cardinality Many-to-many means that each value of one column will be combined with each value from the other column.

Consider setting the filter direction in the setting of the relationship.

Here you will find an introduction to the concept of the star schema and its importance for Power BI datasets: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Here you will find a more in-depth tutorial to data modeling in Power BI: Model data in Power BI - Learn | Microsoft Docs

Hopefully, this provides some help to tackle your challenge.

Regards,

Tom