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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Spudder112
Helper I
Helper I

Date Table Won't link properly

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.

 

Spudder112_0-1676573338854.png

 

 

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)
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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