Problem date relationship Direct_query calendar table/ import table

[quentin36250]

Hello I contact you because I have imported a direct query dataset with a calendar table inside it, i have also a imported table with dates and cost and with the relationship between the two tables, some rows are filtered (so my sum is incorrect) and I do not know why. If i deleted this calendar table (direct query) and replace it by an import calendar table with the same formula and recreate the relationships as it was, it works (sum is correct). Can someone have an idea why it is not working with my direct query calendar table?

Thanks

[miTutorials · Accepted solution]

Kindly check the below.

• Check Data Types: Verify that the date columns in both tables are of the same data type.

• Review Relationships: Double-check the relationships to ensure they are correctly defined and are not causing any filtering issues.

• Check Dates in Calendar table : Make sure you have all the dates from your fact table available in your calendar table 

[Anonymous · Accepted solution]

Hi,@quentin36250 .I am glad to to help you.
Hello,@miTutorials ,thanks for your concern about this issue.

Your answer is excellent! And I would like to share some additional solutions below.

As @miTutorials  said. This is very strange phenomenon. You mentioned in your post that the calendar creation formulas are consistent in DriectQuery and Imporet connection modes, but are correct in Import connection mode.
There are a number of limitations for the DirectQuery connection mode. In the case of calendar tables, calendar tables in DirectQuery join mode do not display calendar hierarchies, and DirectQuery mode does not support drill-down date columns by year, quarter, month, or day.
This may have an impact when you create reports.
Therefore, I recommend you to use mixed mode (DirectQuery+Import) without affecting the report creation and presentation.
Or change DirectQuery to import (please be careful when trying to do this as it is irreversible and you cannot change from import back to DirectQuery).

Here's the official documentation, hope it helps you.
URL:
Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn
Use composite models in Power BI Desktop - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[miTutorials · Accepted solution]

Kindly check the below.

• Check Data Types: Verify that the date columns in both tables are of the same data type.

• Review Relationships: Double-check the relationships to ensure they are correctly defined and are not causing any filtering issues.

• Check Dates in Calendar table : Make sure you have all the dates from your fact table available in your calendar table