Defined value from 2 columns research

[Peter_2020]

Hi all,

I would like to ask for help with following issue:

I am working in Direct query mode with 2 different tables:

1. PROJECT - where I have define name of the project and 

2. PRODUCTION - where I have data from production (PO number, operations, hours...), it looks like this:

production[PO NUMBER]	project[PROJECT]	production[OPERATION]
1060216565	AAA	0020
1066216548	AAA	0040
1065165465	AAA	0090
1066546545	BBB	0020
1066549877	BBB	0030
1065496549	BBB	0040
1066545648	CCC	0020
1066546548	CCC	0050
...	...	...

And I need to create calculated column or measure to get following numbers based on the project and operation name :

PROJECT AAA:

 - operation 0020 - 3 hours

 - operation 0040 - 4 hours

 - operation 0090 - 5 hours

PROJECT BBB:

 - operation 0020 - 1,6 hours

 - operation 0030 - 2,8 hours

 - operation 0040 - 3,11 hours

PROJECT CCC:

 - operation 0020 - 3,6 hours

 - operation 0050 - 2,1 hours

I have tried IF function but there is limitation max 10 repeats.

Thank you for help!

P

[amitchandak]

@Peter_2020 ,

Can you share sample data and sample output in table format?

[Peter_2020]

@amitchandak  Sorry but I am not able to share the files. But example of sample data you can find in my first post and as output I have tried this formula:

Standard time = SWITCH(Production[Operation],

"0020",0.42,

"0030",0.53,

"0040",1.0,

"0080",2.92,

"0090",21.5,

"0110",7.33)

but I need to define somehow that this is just for project AAA and for project BBB,CCC.... I have different values...

[Fowmy]

@Peter_2020 

Where are the hours in your data?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Peter_2020]

@Fowmy  Hours are in PRODUCTION table, but in that case are not imported for me. Hours from these table are totally different like standard hours what I need to calculate.