RELATED Function is not working with no changes to my data

[Caz_16]

I have a report that has a lengthy IF statement that is supposed to look up a fund number from one of two override tables based on if they are not blank. If they are both blank, it just pulls the original fund number which is in the Project Name column. You can see the function below:

CHARGE FUND = IF(NOT(ISBLANK('TIME'[Time Override])), 'TIME'[Time Override],

IF(NOT(ISBLANK(RELATED(TASKS[Activity Override]))), RELATED(TASKS[Activity Override]),RELATED(PROJECTS[Project Name])))

- This is a column formula

- It is placed in the TIME table of my model. 

- The error is as follows "The column 'PROJECTS[Project Name]' either doesn't exist or doesn't have a relationship to any table available in the current context."

- One last thing to note is that a if I replace the RELATED(PROJECTS[Project Name]) with "False" text, it gives me this error instead "The column 'TASKS[ACTIVITY OVERRIDE]' either doesn't exist or doesn't have a relationship to any table available in the current context."

This is a common function used in my organization and it is working on all other reports in our system (Also as a col. and also in the TIME table). It also WAS working on my report previously, but has broken with no change in the report by me. You can take a look at my data model below to see the relationships in the table. As you can see, all the relationships should be there. 

 

 Please help this is a huge issue and breaks my entire report. 

Thanks

[amitchandak]

@Caz_16 , Related can help only one level. means from project to task and task to time.

The workaround is that get the project name in a task using related and from there to the task.

Or use a var in the formula to get project name for the task and then use the task to get it back to Time.

[Caz_16]

@amitchandak @Greg_Deckler 

Here is what I tried:

First, I tried making the relationship direction Both, did not solve the problem, same error.

LOOKUPVALUE and TREATAS are not functions available to be used in that context in an IF statement, so those did not work. 

I tried to add the Project Name column to the TASKS table with the following:
New Column : "Project Name" = RELATED (PROJECTS[Project Name], this did not work, it produced the same error. 

The column 'PROJECTS[Project Name]' either doesn't exist or doesn't have a relationship to any table available in the current context.

That makes this a bit more interesting. I opened up MS Visual Studio Code and verified that my DB had not changed. There is data in the PROJECTS table and the naming nomenclature matched. So no changes on the backend. 

I then deleted the PROJECTS table to start from scratch. I got to a point where I was attempting to add in a SUMMARIZE Table that uses the Projects Fund Number (which is a calculated column in the projects table) as a Primary Key for the Summarize Table. It should be a 1-Many relationship. However, when re-adding the table, PBI would not allow me to make it a 1-Many relationship and produced the following error. 

Formula for the Summary Table:

Project Summary Table = SUMMARIZE(
    PROJECTS,
    
    'PROJECTS'[PROJECT FUND NUMBER],
        "Reporting Category",
        IF([Higher than 400k]=TRUE(),
        "SPONSORED",
        LOOKUPVALUE('TYPE LOOKUP'[Reporting Categories],
            'TYPE LOOKUP'[Fund #],
            PROJECTS[PROJECT FUND NUMBER],
            "UNCATEGORIZED"
            )
        ),
    "Sum of Duration",
    CALCULATE(SUM('TIME'[Duration]))
)

Thoughts as to what would have caused this to break and create a circular dependency?

Thanks.

[Greg_Deckler]

@Caz_16 First, I would move your nested IF statement to a SWITCH(TRUE()...) statement. Second, the issue is your relationship directions. Time to Tasks and Tasks to Project relationships need to be Both. Otherwise, you will have to use something like LOOKUPVALUE or maybe TREATAS, but I would recommend the relationship thing.