Lookup(2,1/ - How can I create New Calculate column in Power BI

[Saxon10]

I have two table are data and report.

Data Table:

In data table contain the following columns are Type, Size A, Size B, Size C and Volume of Size.

Report Table:

In Report table contain the following columns are Item Type, Size A, Size B and Size C.

Desired Result:

I am trying to find out the appropriated size for the item from report table into data table.

Relationship:

In both table (Data and Report) the following columns Type, Size A, Size B and Size C are common.

In Excel I applying below mentioned formula in order achieve my desired result I would like to apply the same logic in power BI by using new calculated column option.

 RESULT SIZE A =IFERROR(LOOKUP(2,1/('DATA '!$G$2:$G$13=1/(1/MAX((('DATA '!$D$2:$D$13>=REPORT!$D3)*('DATA '!$E$2:$E$13>=REPORT!$C3)+('DATA '!$D$2:$D$13>=REPORT!$C3)*('DATA '!$E$2:$E$13>=REPORT!$D3)>0)*('DATA '!$F$2:$F$13>=REPORT!$E3)*('DATA '!$C$2:$C$13=REPORT!$B3)*'DATA '!$G$2:$G$13))),'DATA '!$D$2:$D$13),"")

RESULT SIZE B =IFERROR(LOOKUP(2,1/('DATA '!$G$2:$G$13=1/(1/MAX((('DATA '!$D$2:$D$13>=REPORT!$D3)*('DATA '!$E$2:$E$13>=REPORT!$C3)+('DATA '!$D$2:$D$13>=REPORT!$C3)*('DATA '!$E$2:$E$13>=REPORT!$D3)>0)*('DATA '!$F$2:$F$13>=REPORT!$E3)*('DATA '!$C$2:$C$13=REPORT!$B3)*'DATA '!$G$2:$G$13))),'DATA '!$F$2:$F$13),"")

RESULT SIZE C =IFERROR(LOOKUP(2,1/('DATA '!$G$2:$G$13=1/(1/MAX((('DATA '!$D$2:$D$13>=REPORT!$D3)*('DATA '!$E$2:$E$13>=REPORT!$C3)+('DATA '!$D$2:$D$13>=REPORT!$C3)*('DATA '!$E$2:$E$13>=REPORT!$D3)>0)*('DATA '!$F$2:$F$13>=REPORT!$E3)*('DATA '!$C$2:$C$13=REPORT!$B3)*'DATA '!$G$2:$G$13))),'DATA '!$E$2:$E$13),"")

Data:

TYPE	SIZE A	SIZE B	SIZE C	VOLUME OF SIZE
IND	420	600	440	11.00
IND	640	600	480	9.00
ENG	890	1100	1330	2.00
ENG	1335	1100	2350	1.00
AUS	890	1100	390	5.00
AUS	890	1100	530	3.00
SRI	670	1100	540	4.00
SRI	670	1100	440	6.00
SRI	320	1100	440	10.00
NEW	600	400	400	12.00
NEW	800	600	400	8.00
NEW	1000	600	500	7.00

Report:

ITEM	TYPE	SIZE A	SIZE B	SIZE C	RESULT SIZE A	RESULTSIZE B	RESULT SIZE C
1	IND	250	250	160	420	440	600
2	NEW	600	400	140	600	400	400
3	SRI	300	260	240	320	440	1100
4	NEW	240	160	160	600	400	400
5	AUS	320	320	320	890	390	1100
6	ENG	320	320	320	890	1330	1100
7	NEW	400	300	140	600	400	400
8	NEW	400	300	140	600	400	400
9	NEW	400	300	140	600	400	400
10	NEW	600	400	285	600	400	400
11	NEW	600	400	285	600	400	400
12	NEW	400	300	150	600	400	400
13	NEW	280	230	170	600	400	400
14	NEW	320	320	320	600	400	400
15	NEW	320	320	320	600	400	400
16	NEW	320	320	320	600	400	400
17
18	NEW				600	400	400
19	NEW

Snapshot Data and Report Table with formula:

[Arul]

Hi @Saxon10 ,

Could you please let me know your desired result from this two table?

Thanks,

-Arul.

[Saxon10]

Yes. The desired result based on the this two tables. 

[Arul]

@Saxon10 ,

What are you expecting as a output values?

Thanks,

-Arul.

[Saxon10]

[Arul]

@Saxon10 ,

I think this is data table 2. Isn't it?

[Saxon10]

Yes. This is Data Table: 

Report Table:

I am marked in red colour that's my expected result.

[AlexisOlson]

DATA has multiple rows for each TYPE. How do you decide which row you want to look up values from?

[Saxon10]

Hi,

Herewith attached the Excel file for your reference https://www.dropbox.com/scl/fi/t6hj9edyt9yfimgzl6nj5/SP.xlsx?dl=0&rlkey=tugme6d487nue17fambsqe6oz

[AlexisOlson]

That doesn't answer my question. I can't determine how you intend for the logic to work from those rather opaque array formulas, so I'll rephrase.

Given that there are multiple rows for each TYPE in the DATA table, which row do you want to look up the sizes for in the REPORT calculated columns?

[Saxon10]

Hi,

Thanks for your reply on this post.

In data sheet I used volumn in order to pickup the value from data sheet to report sheet.

I found the formula in Excel not sure how can apply in Power BI.

Please advise is that possibile in Power BI.

[Saxon10]

Yes. Herewith attached the PBI file for your reference.

https://www.dropbox.com/s/0n1hr0b92o4gfhx/LOOKUP%20VALUE%202%2C1.pbix?dl=0

[Saxon10]

Attached the desired out on the snapshot. I will attach power bi in a min.