Creating new column using values from column A, conditional on columns B and C

[Anonymous]

Hi, I'm struggling a lot with this problem, any help would be appreciated

I have a table like this:

A	B	C
24	1	7
14	2	7
64	3	7
24	4	7
13	5	7
47	6	7
62	7	7
34	8	14
63	9	14
35	10	14
74	11	14
35	12	14
75	13	14
35	14	14

I want to create a new column such that:

If B != C for the row, then we want the value from A WHERE the C for the row in question = B from any of the rows.

Else if B == C, we wnat the value from A.

The resulting table looks like:

A	B	C	New column
24	1	7	62
14	2	7	62
64	3	7	62
24	4	7	62
13	5	7	62
47	6	7	62
62	7	7	62
34	8	14	35
63	9	14	35
35	10	14	35
74	11	14	35
35	12	14	35
75	13	14	35
35	14	14	35

I have tried:

Weekend inventory = if(

[B]<>[C],SELECTCOLUMNS(filter(Table,[B]=[C]),"temp",[A]),

[A])

But this gives me an error which I assume is because there are multiple matching B and C values...

Any help would be really appreciated!!!!!!

[Anonymous · Accepted solution]

Hi,

Try this formula

New Column = 
VAR a_val = 'Table'[A]
VAR b_val = 'Table'[B]
VAR c_val = 'Table'[C]
RETURN
IF (
    b_val <> c_val,
    LOOKUPVALUE( 'Table'[A], 'Table'[B], c_val),
    a_val)

[Anonymous]

Thanks so much for your reply!!!

But I am getting this error for some reason:

"A table of multiple values was supplied where a single value was expected."

[Anonymous]

Look I think your formula is pretty sound - I'll go check my data to see if there are multiple matches between columns B and columns C.....

Thanks for your help!!!