cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Post Prodigy

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

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:

12 REPLIES 12
Super User

Hi @Saxon10 ,

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

Thanks,

-Arul.

Proud to be a Super User!

Post Prodigy

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

Super User

What are you expecting as a output values?

Thanks,

-Arul.

Proud to be a Super User!

Post Prodigy

Super User

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

Proud to be a Super User!

Post Prodigy

Yes. This is Data Table:

Report Table:

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

Super User

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

Post Prodigy
Super User

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?

Post Prodigy

Hi,

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.

Post Prodigy

Yes. Herewith attached the PBI file for your reference.

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

Post Prodigy

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