Date Table Won't link properly

[Spudder112]

transactiondate is from my Data table, the other columns are all from my Date Table. Week Rank only works when paired with the other columns from the other table, there is a many to one relationship between transactiondate and Date.

Additonal notes for current structure of my date table...

_Dates = ADDCOLUMNS(CALENDARAUTO(),

    "YEAR", FORMAT([Date],"yyyy"),

    "MonthNo", Month([Date]),

    "Month", FORMAT([Date], "MMM"),

    "Quarter", FORMAT([Date],"\QQ"),

    "YearMonth", FORMAT([Date], "YYYY"),

    "WeekdayNo", WEEKDAY([date], 2), //1-Sun..Sat, 2-Mon..Sat

    "Weekday", FORMAT([Date], "dddd"),

    "WeekNo", WEEKNUM([Date], 2),

    "Week", "W" & WEEKNUM([Date],2) )

Week End Date = '_Dates'[Date] - MOD('_Dates'[Date]-1,7)+5

Week Rank = RANKX(('_Dates'),'_Dates'[Week End Date],,ASC,Dense)

[MFelix · Accepted solution]

Hi @Spudder112 

When you use dimension tables (in this case the date columns) you must always use the dimension table on your visualizations, in this case when you use your calculation with the data from your fact table you are always getting the same result because of the filter path between the transaction table and the date table.

On the one to many you have a filter that is coming from the date table to the transactions table, the other way around is not possible so it's like there is no relationship between the table and you get always the same result.

Not sure what you are trying to achieve with this visualization but you must use the Date from the date table and not the transcation date.