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
Caz_16
Helper II
Helper II

RELATED Function is not working with no changes to my data

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. 
 
 Rels1.PNGtable1.PNGtable2.PNG
 Please help this is a huge issue and breaks my entire report. 
 
Thanks
3 REPLIES 3
amitchandak
Super User
Super User

@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.

@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. 

 

 
 

Capture.PNG

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
Super User
Super User

@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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors