Relationship between two tables

[Dunner2020]

Hi there,

I have two tables: one with five years' average value and the other is the date table. Five years' average value has one to many relationships with the date table. 'RY Month' column of five-year average is connected with the Month Number column of date table as shown in the snapshot:

Both columns have the same data type i.e. whole number. 

I created the line chart in which the y-axis contains the average cumulative SAIDI column value and the x-axis contains 'RY Month' columns from the five-year average table as shown in the picture:

However, when I changed the x-axis value field from 'RY Month' (from five-year average table) to 'Month Number' (from date table) , it shows a flat line (as shown in the figure) which should not be the case.

My expectation was as the relationship between the two tables is established, so there should make any change in the graph when I change the x-axis values from one table to another. Could anyone explain where the potential problem be?

Here is the link of data of both tables:

https://1drv.ms/x/s!AucycxZHFe9TjT0-EM1mOD6OFkmo?e=kCm2jE

[edhans]

Yes. your relationship is backwards @Dunner2020. It should be from the Date table (one) to the FACT table (many). You need a real date in your FACT table then link that to the Date in the Date table. This is why it is a flat line, the date table isn't filtering the other table. See the direction of the arrow? 

And don't enable bi-directional. That doesn't always fix it and is the wrong approach here.

Without being able to see what kind of data is in the RY Month column, I cannot assist in making it a valid date. I'd need data, per below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Dunner2020]

@edhans , I did not understand the direction of the line. The data was too big and the portal did not let me paste. So I have uploaded on one drive and pasted the link in the actual post. 

[edhans · Accepted solution]

The direction of the line shows the filter direction. So your data table is filtering the date table. That is backwards. The Date table is a DIM (Dimension) table and should always filter the FACT (or data) table.
Do this:

Add this formula to your data table and call it Date. This is in Power Query. Add a new custom column. This is the formula.

if Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))) > DateTime.Date(DateTime.LocalNow())
then Date.AddYears(Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))), -1) 
else Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow()))))

Now, that may not be right. What it returns is this (I hid columns for the sake of space)

Now connect that date column to the Date column in the Date table. It should be a one to many from the Date table pointing to the fact table. 

Now the date table fields should be used in your visuals and will work.

If that is not the correctly calculated date, show me what it should be. Basically I said get the month name (jan, feb, mar) and make it the last day of month for this year, unless it is the future, then make it last year.

[Dunner2020]

Thanks @edhans  for explaining the filter issue. I entered the date column in the fact table and connected with the dim table and it worked as expected. You are my saviour.

[edhans]

Great @Dunner2020 - glad it helped. This is the beginning theory of the Star Schema, which is critical to most successful Power BI Models. MS has a page, and there are some good articles and books on it as well.

Microsoft Guidance on Importance of Star Schema