Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
How do I troubleshoot this?
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |