Forum Discussion

IffyN's avatar
IffyN
Regular Visitor
8 months ago
Solved

Dimdate table isn't working

I am new to using Power BI. I'm trying to create a PBI report from a semantic model I created on Fabric, but I get this error. Because my model didn't have a date table, I created one on Fabric using...
Need Help
  • syahmisi98's avatar
    syahmisi98
    8 months ago

    You have a few options to fix it:

    1. Use a truly unique key for the relationship

    Instead of MonthNumber, use a column like:

    • FullDate (YYYY-MM-DD)
    • DateKey (e.g., 20250101)
    • YearMonth (e.g., 202501)

    These are unique at the date or month level and are suitable for the one-side of a relationship.

    2. Create a unique Year-Month key

    If your fact table is at a monthly granularity, create a new column in DimDate:

    YearMonth = dimdate[Year] * 100 + dimdate[MonthNumber]


    This will ensure values like 202501, 202502, etc., which are unique.

amitchandak's avatar
amitchandak
Super User
8 months ago

IffyN , Seem like you are joining with the year column, not date. That is why there is an issue in relation. You can create a Date table using Dataflow Gen 2, SQL or PySpark or even using DAX in the Semantic model 


This for Direct query , but can be used in fabric Warehouse or you can convert this to spark SQL for Lakehouse 
https://amitchandak.medium.com/power-bi-direct-query-date-table-in-sql-server-b5f4fe0f6d3d

For DF 2

https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1


Add a Table in the Semantic Model, All new models support DAX tables 

Date =CALENDAR(date(2018,01,01), date(2021,10,31))
,"Year", YEAR([Date])
,"Month No", MONTH([Date])
, "Qtr No", QUARTER([Date])
,"Month",FORMAT([Date], "mmmm")
, "Month Year", FORMAT([Date], "mmm-yyyy")
,"Qtr", FORMAT([Date],"YYYY-\QQ")
,"Month Year Sort", Year([Date])*100 + Month([Date])
, "Year Week No", YEAR([Date])*100 + WEEKNUM([Date],1)
, "Week No",  WEEKNUM([Date],1)
, "Week Day", FORMAT([Date], "ddd")
, "Weekday no",  WEEKDAY([Date],1)
, "Start Of Month", EOMONTH([Date],-1)+1
, "End of Month", EOMONTH([Date],0)
, "Start of Year", EOMONTH([Date],-1* MONTH([Date]))+1
, "End of Year", EOMONTH([Date],12 -1* MONTH([Date]))
, "Start of Qtr", var _rem=  if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3))
                RETURN EOMONTH([Date], -1*_rem)+1
, "End of Qtr", var _rem=  if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3))
                RETURN EOMONTH([Date], 3- _rem)
,"Week Start Date", [Date]- WEEKDAY([Date],1)+1
,"Week End Date", [Date]+7- WEEKDAY([Date],1)
 , "Start of FY", Date(if(month([Date]) <4,Year([Date])-1, Year([Date])),4,1)
, "End of FY", Date(if(month([Date]) <4,Year([Date]), Year([Date])+1),3,31)
)