Problem with RELATED function: "The column ither doesn't exist or doesn't have a relationship..."

[Anonymous]

Hello everyone,

I have the following problem:

I have two tables, one contains data about recruitment projects [Jobs] and the second one is a calendar [Calendar].

In the Calendar Table I have a Calculated column, which checks whether the date is in the current month (current indicates the last month of data from my data source) and returns different text depending on whether it is correct or no.

The code is below:

IsCurrentMonth = 
IF (
    AND (
        YEAR ('Calendar'[Date]) = YEAR ( [ReportingDate] );
        MONTH ('Calendar'[Date]) = MONTH(MAX(Jobs[Created Date]))
    );
    "Current Month";
    "Other"
)

I have a relationship between these two tables based on a date the project was created: Jobs[Created Date] and a date Column Calendar[Date].

I want to have another column in the Jobs table that checks whether the salary for the job was provided in the right format (it cannot be lower than 500) and I want this information to be available only for the current month (I cannot use filters on the visuals as there are other columns in the table tha present data for the whole year), so I used this code below.

MonthNoSalary =
AND (
    OR ( Jobs[Salary From] < 500; Jobs[Salary To] < 500 );
    RELATED ( 'Calendar'[IsCurrentMonth] ) = "Current Month"
)

It worked for some time but then I decided to change from columns in text format to TRUE/FALSE and then everything stopped working and I get the following message:

"The column 'Calendar[IsCurrentMonth]' either doesn't exist or doesn't have a relationship to any table available in the current context."

I tried to change it back to Text but it still doesn't work... And I have an older file where I did not change and everything works there.

I would be grateful for any help as I am running in circles...

[v-huizhn-msft]

Hi @Anonymous,

You create calculated coulmn in Calendar table, it work fine. While you create calculated column in Jobs table, you get the error, right?

>>It worked for some time but then I decided to change from columns in text format to TRUE/FALSE and then everything stopped working and I get the following message.

Did it return the error message befor you change the text format? I am not able to reproduce your scenairo, could you please share the .pbix file for analysis?

Best Regards,
Angelia

[Anonymous]

Hi Angelia,

I tried to clear to file to send it to you and I discovered that the reason for the error is a relation that I have between the Calendar table and another table (Monthly Past Data).

The relationship is inactive and when I remove it completely the calculated columns start working...

Below is the map of all the relations I have between my tables.

Relations between tables

I have another file with the exact same relations and everything works - you can download the files on the link below:

https://we.tl/oXD3Invnrw

Not sure if they will work without the data sources, in case just let me know.

Thank you in advance!

Regards,

Tomasz

[v-huizhn-msft]

Hi @Anonymous，

Do you have resolved your issue? If it is, I am very glad to hear that the calculated columns start working. Please mark the corrensponding reply as amswer which will help more people. Thanks for understanding.

Best Regards,
Angelia

[Anonymous]

Hi Angelia,

no, unfortunately it still doesn't work.

I have just narrowed the cause of the problem to be the relationships between tables and not the calculated columns as well.

The problem still exists and honestly I have no idea why it works in one file and doesn't work in another...

Regards,

Tomasz

[v-huizhn-msft]

Hi @Anonymous，

Please verify the difference between fine file and the file existing the problem. Especially the rationship between tables. Or could you please share your .pbix file for further analysis? Thanks a lot.

Best Regards,
Angelia