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
maiios
Frequent Visitor

How to graph the instances of a column of dates?

I swear I have done this a dozen times, and am not sure why its not working right now.

 

I have a date table, and a table with a datetime column. I changed the type of the datetime column to 'date', and made a relationship between the date table and that column. The date table looks like the following. 

 

Dates2 = 
VAR BaseCalendar = 
    CALENDARAUTO()
RETURN 
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR( BaseDate )
        VAR MonthNumber = Month( BaseDate )
        RETURN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT( BaseDate, "mmm"),
            "Year Month", FORMAT( BaseDate, "yy-mm" ),
            "Year Week", YEAR ( BaseDate ) & FORMAT(WEEKNUM ( BaseDate, 2 ),"00") 
        )
    )

 

 

I created a measure to count the instances of each date. The relationship is active, but I did USERELATIONSHIP to be pedantic. 

 

 

Created count = CALCULATE(COUNT('Case'[CreatedDate]), USERELATIONSHIP(Dates2[Date], 'Case'[CreatedDate]))

 

 

However, there seems to be an issue with the relationship. When I try to use the data, it doesn't seem to relate.

 

maiios_0-1608660131957.png

 

How do I troubleshoot this?

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Generally the dimensional column should be used as the first column in a table (if you care about the join type)

 

Also, instead of changing the display type of CreatedDate you can try to create a calculated column from it, throwing away the time part and keeping only the date part. Then change your relationship to that column.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Generally the dimensional column should be used as the first column in a table (if you care about the join type)

 

Also, instead of changing the display type of CreatedDate you can try to create a calculated column from it, throwing away the time part and keeping only the date part. Then change your relationship to that column.

I really don't understand why it worked when I basically transformed the data into a string, and then back to a date, but it did. Seems like it should have worked when I just changed the column type, but oh well. 

 

Thanks for the help. 

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.