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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.