Forum Discussion
Create 'IF' function across 2 tables
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.
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.
5 Replies
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" )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.
Make sure you're creating a new column, not a measure. You wouldn't be the first to make that mistake.
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.
