Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lukasjar
Resolver I
Resolver I

Calendar date relations returns blank?

 

Hello community!

I cannot find the reason to why the table below is not identifying the correct Year for the columns AutoTime,RunTime...

As you can see on the pictures for table relationsships I have a One to Many relationship between the Calendar table and both the StoppageData and OeeData table. StoppageData is presenting itself correctly, but OeeData (AutoTime) is incorrect. Removing StoppageData relation doesnt change anything.

Most of the time in AutoTime is in the top row, with blank Year. RunTime.... is a different calculation of AutoTime.Most of the time in AutoTime is in the top row, with blank Year. RunTime.... is a different calculation of AutoTime.

 

Date format on OeeData and StoppageData is identical.

image.pngOeeData StartTime format, same as in StoppageData.OeeData StartTime format, same as in StoppageData.

 

Relationsships are One to Many from the calendar i am using.

Relationsshipts are drawn to a calendar table One to Many. StoppageTimeHours comes out correct. Removing StoppageData link does not change anything.Relationsshipts are drawn to a calendar table One to Many. StoppageTimeHours comes out correct. Removing StoppageData link does not change anything.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Your starttime has time stamp

 

Create a a new column

Start date = Table[Starttime].date

Or

Start date = format(Table[Starttime],"mm/dd/yyyy") //Change data type to date. Use it to join

 

Appreciate your Kudos.

View solution in original post

2 REPLIES 2
DavidGolden
Helper I
Helper I

Hello, I have a similar trouble dealing with a column of dates from Table [Fec.Denuncia], wich is related to the Dates (CALENDARAUTO). For some reason that I cannot understand yet, some of the dates in Table[Column] are linked to BLANK values (blanks that I cannot see in Dates!). Since this relation is not active, the counting of elements through CALCULATE when utilizing USERELATIONSHIP return an incorrect result. 

For example, when exploring through Data view, 452 elements are listed in January, 2024, by [Fec.Denuncia]. But when filtering with the related Dates, some of them are missing.

 

See pictures below please, when filtering by [Fec.Denuncia] is the same range as Dates, only 117 of 452 elements are counted, and the remaining 335 elements are seen as blank dates of Date (CALENDARAUTO). I checked formats are of Date type, also that all dates are in Dates. I also tried building a new column from [Fec.Denuncia] but i didnt worked.

Result when BLANK Dates are filtered together with January in ColumResult when BLANK Dates are filtered together with January in Colum1-.JPGResult when all Dates are activeResult when all Dates are active

I appreciate any help with this issue.

amitchandak
Super User
Super User

Your starttime has time stamp

 

Create a a new column

Start date = Table[Starttime].date

Or

Start date = format(Table[Starttime],"mm/dd/yyyy") //Change data type to date. Use it to join

 

Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.