none
Error trying to relate tables "...either doesn't exist or doesn't have a relationship to any table available in the current context"

    General discussion

  • I have two tables;

    1. The first is my Order table which has a single row for each unique order - there are no duplicate order numbers.The order id column here is called OrderID.
    2. The second is my Submissions table which is similar to a transactions table, there are multiple submissions for every order. This table also contains an OrderID column that contains the relevant OrderID.

    The submission table also contains a calculated field called Date and a calculated field called MaxDate field which is the date of the most recent submission that relates to any particular order in cases where there are multipple.

    My goal is to add a column to the Order table this MaxDate column. The reason for duplicating the information rather than linking the tables is because I need to create more calculated columns in the Orders table based on this value.

    I have created a relationship between the two tables however when I use:

    =RELATED(Submissions[MaxDate]

    I get this error:

    "...either doesn't exist or doesn't have a relationship to any table available in the current context"

    I don't really understand why this would be, because MaxDate definitely exists and contains only numerical values. What is more strange is that if I try and combine the data through a pivot table it actually works! I can create a pivot with OrderID in the first column and then associate it with MaxDate from the Submissions table as the value. 

    Can anyone help me understand what is the problem here?

    P.S. if it is useful the formula I'm using to calculate the MaxDate is:

    =MAXX(FILTER(Submissions,Submissions[OrderID] = EARLIER(Submissions[OrderID])),[Date])

    Thanks.


    Friday, July 19, 2013 4:28 PM

All replies