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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
juaninavarrete
Frequent Visitor

Calendar Table Create Relationshiop But Doesn´t Work

Hi!

 

I have an unusual problem with my calendar Table.

I have a Fact Table called RegistroSolicitudes, in wich it has a column name "Created" (Date Time), and I have my calendar table created by:

Calendar = ADDCOLUMNS ( CALENDAR(DATE(YEAR(MIN(RegistroSolicitudes[Created])),1,1),DATE(YEAR(MAX(RegistroSolicitudes[Created])),12,31)),
"Year", FORMAT([Date], "yyyy"),
"MonthNo", MONTH([Date]),
"Month", FORMAT([Date],"MMM"),
"Quarter", FORMAT([Date],"\QQ"),
"YearMonth", FORMAT([Date],"YYYY-MM"),
"WeekdayNo", WEEKDAY([Date],2), //1-Sun..Sat, 2-Mon..Sat
"Weekday", FORMAT([Date],"ddd"),
"WeekNo", WEEKNUM([Date], 2),
"Week", "W" & WEEKNUM([Date], 2),
"IsWorkingDay",NOT WEEKDAY([Date]) IN { 6,7 } )
 image.png
When I create the relationship between Calendar[Date] and RegistroSolicitudes[Created], the relationship is created and is active. But I cannot use it in the Report, for example in the RegistroSolicitudes table I create an Calculated Column that is:
 
Dia Habil =
RELATED('Calendar'[IsWorkingDay])
 
And none value is returnet. In the same way if try to create a Table in the report with the Date columns and other columns from the RegistroSolicitudes table, none date appears.
image.png
 
image.png
 

image.png

 

¿Any ideas what to do?

 
 
1 ACCEPTED SOLUTION

Thanks to @amitchandak I couldn't create that new calculated column

image.png

Instead I had the idea to create the idFecha column in the two tables as follows:

idFecha a YEAR(RegisterSolicitudes[Created])*10000+MONTH(RegisterSolicitudes[Created])*100+DAY(RegisterSolicitudes[Created])
And create the same column with the Date column in the Calendar table and relate these two columns. It's a play!
Thank you so much for your help!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@juaninavarrete , Created has a timestamp in it . so create a date like this

 

Created Date = [Created].date

 

And join this with date of date table inplace of created and use.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Thanks to @amitchandak I couldn't create that new calculated column

image.png

Instead I had the idea to create the idFecha column in the two tables as follows:

idFecha a YEAR(RegisterSolicitudes[Created])*10000+MONTH(RegisterSolicitudes[Created])*100+DAY(RegisterSolicitudes[Created])
And create the same column with the Date column in the Calendar table and relate these two columns. It's a play!
Thank you so much for your help!

Hi @juaninavarrete ,

Glad to hear the issue is solved😀. You can accept the suitable reply as solution, that way, other community members could easily find the answer when they get same issues.


Best Regards,
Yingjie Li

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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