Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 } )
 
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.
 
 

 

¿Any ideas what to do?

 
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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!

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!
      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

        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