Create set/ order value from single row in set

[pdeubner]

Dear,

I currently have an issue where I seemingly meet my limits - even though I thought it should be quite easy to do.

In a table, I have a lot of data out of our ERP system

They have the following structure (as an excerpt)

order no.	phase	ressource	head ressource	roughly 200 additional columns
123456	1010	material
123456	1010	material
123456	2010	UMP_01
123456	2010	UMP_01
123456	2010	UMP_01
123456	4010	personnel
123456	4010	personnel
123457	1010	material
123457	1010	material
123457	2010	UMP_02
123457	2010	UMP_02
123457	4010	personnel
123457	4010	personnel

We have several orders that have reports using different ressouces. The number of reports against the order are varying. 

In order to summarize based on the ressource (=production machine), I would like to have the ressource of phase 2010 in the column head ressource for all rows of the corresponding order.

What I did so far was to create a key column, consisting of order and phase and trying to do a LOOKUPVALUE. However, since there are multiple entries, I never got it working.

So - any help would be highly appreciated.

Thank you!

Regards

Philip

[pdeubner]

Hi @danextian ,

thank you for your swift feedback and your remarks.

Based on my sample data, I would expect in all rows for 

order 123456 stating UMP_01 in the column head ressource and for

order 123457 stating UMP_02 in the column head ressource

And thank you as well for your remarks on the performance. I will have a look into the needed columns as soon as the report is finished.

[danextian · Accepted solution]

Is this what you're looking for?

resource2 = 
CALCULATE (
    MAX ( Data[ressource] ),
    FILTER ( ALLEXCEPT ( Data, Data[order no.] ), Data[phase] = 2010 )
)

[pdeubner]

Excactly like this - thanky you for your effort!

[danextian]

Hi @pdeubner 

I don't quite get this statement

 I would like to have the ressource of phase 2010 in the column head ressource for all rows of the corresponding order.

Using your sample data, what result do you expect?

Also, do you really need  those 200 columns or you only think you need them? While Power BI can handle that much columns, it can degrade the performance of your model.