Hi everyone,
I’m currently working on a project in Microsoft Fabric, specifically in a workspace where I am building a Sales Datamart for my organization. Below is a detailed description of my workflow and the issue I’m encountering:
Project Workflow:
I ingest tables from an on-premise SQL database into Fabric.
The tables go through a transformation process until they are stored in the Silver and Gold layers of the Data Lake.
In the Gold layer, I have tables ready for modeling and analysis in the Datamart.
In the model, I want to use a calendar table with the necessary attributes for my organization.
The Problem:
When I try to set the calendar table as a date table in the Datamart within Fabric, the platform does not allow it, regardless of the different options I’ve tried. The same issue occurs when I try to set the date table in Power BI while connecting directly to the Datamart. However, if I use a local semantic model in Power BI, I can successfully set the table as a date table.
Steps I’ve Tried:
Bringing the Calendar Table from On-Premise SQL:
Ensured the Date column is unique, has no duplicates, no blank values, and no repeated records.
Set the column data type to Date and also tested with DateTime.
Creating the Calendar Table in Fabric:
Used a notebook in the Fabric workspace to generate the table.
Created the table with DAX using the CALENDARAUTO() function.
Built the table in Power Query, ensuring its structure meets the requirements.
Validations Performed:
Verified that the column in the table meets the expected criteria:
Unique values.
Proper data type (Date or DateTime).
No blank cells or duplicates.
When attempting to set the calendar table as a date table, the option to mark the table as a date table is not enabled. Similarly, attempting to set the date table directly within Power BI while connected to the Datamart also doesn’t work. However, when importing the calendar table into a local semantic model in Power BI, I am able to successfully set it as a date table without any issues.
Question:
Has anyone faced a similar issue when trying to set a table as a date table in a Datamart within Microsoft Fabric? What additional configurations or steps might be missing to resolve this?
Any suggestions or recommendations would be greatly appreciated. Thank you for your help!
