Reference another table in a calculated column

[JemmaD]

Hi there

I'm trying to be fancy and i've got a date dimension table which has a 'working day' indicator of 1 if it's a working day, and 0 if it's a non-working day.

My Report table has a 'days to complete' column indicating how long it's taken a person to complete a task from the start date.

If the task came in on a non-working day they get 4 days to complete to be inside service level. If the task came in on a working day they get 2 days to complete to be inside service level.

I am trying to build a column to indicate if it's inside or outside of service by using the date dimension table 'working day' indicator. 

I'm getting an error that the experession refers to multiple columns which cannot be converted to a scalar value.

Below is my syntax, can you help me get it right? 

Inside Service = 
    IF ( FILTER ('DimDate', 'DimDate'[WorkingDay] =0 ) && 'Report'[Days to Complete]>=4, 0,
    IF ( FILTER ('DimDate', 'DimDate'[WorkingDay] =1 ) && 'Report'[Days to Complete]>=2, 0,
       1 ) ) 

[amitchandak · Accepted solution]

@JemmaD , If you have two dates you can use networkdays function to get working days :https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c

If you want to access a column from another related table related and realtedtable function can help.

there are two more way for unrelated tables

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8