Create 'IF' function across 2 tables

[powerbidu]

Hi all, I have the below dataset:

Table 1
Identifier	Deadline
A	5
B	6
C	7
D	8
E	9
Table 2
Identifier	Month End	Days taken	Is 'Days taken' more than deadline?
A	28-Feb	6	Yes
A	31-Mar	3	No
A	30-Apr	5	No
A	31-May	7	Yes
B	31-May	8	Yes

I am trying to create the calculated column "Is 'Days taken' more than deadline?" in Table 2.

I used the below function:

Is 'Days taken' more than deadline? = IF('Table 2'[Days taken] > 'Table 1'[Deadline], "Yes", "No")

However, I got the below error message:

A single value for column 'Deadline' in table 'Table 1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Can anyone advise why the error is flagging and what formula I could write to get the expected result?

Thanks in advance.

[vivek31 · Accepted solution]

HI @powerbidu,

you can use lookupvalue function and create a column 

Day taken more = 
var identifier = LOOKUPVALUE(Table__1[Deadline],Table__1[Identifier],Table__2[Identifier]) 
RETURN
IF(Table__2[Days taken] > identifier ,"YES","NO")

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

[johnt75]

Create a relationship from Table 1 to Table 2 based on the identifier column. Then you can use

Is 'Days taken' more than deadline? =
IF ( 'Table 2'[Days taken] > RELATED ( 'Table 1'[Deadline] ), "Yes", "No" )

[powerbidu]

Hi, I got the error 'The column 'XXX[XXX]' either doesn't exist or doesn't have a relationship to any table available in the current context." I checked and had set up the relationship and referenced the column correctly. Any idea on what could be wrong? Thank you.

[johnt75]

Make sure you're creating a new column, not a measure. You wouldn't be the first to make that mistake.

[powerbidu]

Yes it is already a column and not a measure, but the error pops up.