Result of Lookupvalue from the same column

[Commons94]

Following is the sample dataset. The result columns is where the results from above rows must be filled in.

I do know how this can be done in Excel but can't find a way to do it in power BI.

the procedure to find the results is to check for value 'No' in 'Column A', when true, the lookup function needs to occur. The lookupfunction should proceed like this: if the value in 'Column A' is true (= "NO") then the corresponding value in 'column E' should be searched in 'Column D'. when there is a match the respective values from 'Result date column' and 'Result qty column' must be entered where the 'Column A' value is true.

In excel the formula would be: = IF(A9="NO",XLOOKUP(E9,D:D,B:C)). Suggestions would be most welcome. thanks in advance.

Column A	Result date column	Result qty column	Column D	Column E
Initial Value	04/08/2022	154	X1
			X2
			X3
			X4
			X5
			X6
			X7
NO	04/08/2022	154	Y1	X1
NO	04/08/2022	154	Y1	X1
			Y3	X3
			Y4	X4
			Y5	X5
			Y6	X6
			Y7	X7
			Y8	X8

[Commons94]

Thanks a lot for your help. Column B is the result column, I guess this can't be done on Power BI.

[v-yangliu-msft · Accepted solution]

Hi  @Commons94 ,

Here are the steps you can follow：

1. Create calculated column.

Column =
IF(
    'Table'[Column A]="NO",
    MAXX(
        FILTER(ALL('Table'),'Table'[Column E] in SELECTCOLUMNS('Table',"D",'Table'[Column D])&&
        'Table'[Column E]=EARLIER('Table'[Column E])
        ),
        [Column B]),BLANK())

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mahesh0016]

@Commons94 Please can you share ENDOUTPUT in table.

[Commons94]

@Mahesh0016  I have provided here an elaborated sample dataset, is this somewhat helpful? thanks again!

Column A	Column B	Column C	Column D	Column E
	04/08/2022	154	X1
	10/08/2022	256	X2
	11/09/2022	68	X3
	22/10/2022	126	X4
	05/11/2022	550	X5
	08/12/2022	119	X6
	15/01/2023	154	X7
NO	04/08/2022	154	Y1	X1
NO	04/08/2022	154	Y1	X1
NO	11/09/2022	68	Y3	X3
NO	22/10/2022	126	Y4	X4
NO	05/11/2022	550	Y5	X5
NO	08/12/2022	119	Y6	X6
NO	15/01/2023	154	Y7	X7
			Y8	X8
NO	04/08/2022	154	Z1	Y1
			Z2	Y2
NO	11/09/2022	68	Z3	Y3
NO	22/10/2022	126	Z4	Y4
NO	05/11/2022	550	Z5	Y5
NO	08/12/2022	119	Z6	Y6
NO	15/01/2023	154	Z7	Y7
			Z8	Y8
			Z9	Y9
			Z10	Y10

[Mahesh0016]

@Commons94 
Result_Date_Column = IF(Table[Column A] = "NO",

LOOKUPVALUE(Table[Column E],Table[Column D],Table[Date_Column]))

Result_qty_column = IF(Table[Column A] = "NO",

LOOKUPVALUE(Table[Column E],Table[Column D],Table[QTY_Column]))

@Commons94 i hope this helps if this not help Please share your ENDOUTPUT.

[Commons94]

@Mahesh0016 Thanks for your quick reply. when there is a match in the if statement and with the lookup function, I need to extract the value from the result date column itself. Hence, I'll be running a loop in the result date column itself. hope it helps in understanding the problem.