Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Saxon10
Post Prodigy
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:

 

 

Saxon10_2-1619389157639.png

 

Saxon10_3-1619389174000.png

 

 

 

 

 

 

 

 

 

 

12 REPLIES 12
Arul
Super User
Super User

Hi @Saxon10 ,

 

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

 

Thanks,

-Arul.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


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

@Saxon10 ,

 

What are you expecting as a output values?

 

Thanks,

-Arul.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Saxon10_0-1619467027764.png

@Saxon10 ,

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Yes. This is Data Table: 

Saxon10_0-1619467936499.png

 

Report Table:

 

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

 

Saxon10_1-1619468120834.png

 

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

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?

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.

Yes. Herewith attached the PBI file for your reference.

 

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

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.