Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
5 years ago
Solved

Relationship between two tables

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

 

Need Help
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    5 years ago

    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.

     

     

     

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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's avatar
      Dunner2020
      Post Prodigy
      5 years ago

      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's avatar
        edhans
        Community Champion
        5 years ago

        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.