Date Table in Direct Query is not working || Emergency

[PremK]

My Data source in MongoDB Atlas. i have created a View table in MongoDB Atlas and connected it to PBI through Direct Query mode for real time data visuals. 

Created Date Table in 2 ways 
1. static table  = CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31)).  then marked it as Date table and given relationship with the main table using date column.

2. Dynamic table  = CALENDAR( MIN(Main table Date column), Today() ).  then marked it as Date table and given relationship with the main table using date column.

Both options didnt work. added a slicer with date table date but its not filetering the report. for testing i addedd a slicer with Main table Date column and its filtering the data, but without a Date table how can i create Time intelligence visuals trend charts ?

need solution to create a Date table in direct query mode
 

[PremK · Accepted solution]

Hii All, nothing worked at all.

so i created a View collection in MongoDB with all the required fields and date fields. then connected with PBI and it worked 90%

[V-yubandi-msft]

Hi @PremK ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[V-yubandi-msft]

Hi @PremK ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @PremK ,

Thank you for engaging with the Microsoft Fabric Community. It seems the issue may be related to how DirectQuery interacts with MongoDB Atlas. The recommended approach is to create a Date Table directly in MongoDB Atlas and link it to Power BI.

1. Build a Date Table in MongoDB Atlas using an aggregation pipeline or SQL Interface to cover your required date range. 

2. Connect the Date Table to your main dataset in MongoDB before importing it into Power BI. 

3. Check for query folding support, as certain transformations may not function correctly in DirectQuery.

4. Test filtering behavior using a smaller dataset to identify any potential limitations in Power BI.

Because DirectQuery has some restrictions, including schema changes and row limits, confirming these aspects will help ensure the Date Table operates correctly in Power BI.

Thanks for your responses @maruthisp & @Akash_Varuna .

If my response solved your query, please mark it as the Accepted solution to help others find it easily. And if my answer was helpful, I'd really appreciate a 'Kudos'.

[maruthisp]

Hi PremK,

Can you try below steps:

Use composite model:

Keep your main table in DirectQuery.

Create a Date table in Import mode - CALENDAR(DATE(2025, 1, 1), DATE(2025, 12, 31)

Use “Manage Relationships” to create a relationship.

Enable “Storage Mode: Dual” for the Date table if possible.

Note: Composite models may not be fully supported with MongoDB connectors depending on the connector you're using (ODBC, custom, or native).

Please let me know if you have further questions. Thanks in advance!

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X 

[Akash_Varuna]

Hi @PremK  you can try by creating a physical date table directly in MongoDB Atlas covering your needed date range and importing it via DirectQuery. Ensure the date columns in both tables match in format (date-only) and build a relationship between them. Use the date table slicer to filter your visuals and mark it as a Date Table in Power BI for time intelligence functions. Avoid using DAX-generated date tables in DirectQuery mode, as they often don’t filter correctly.