Forum Discussion
Create set/ order value from single row in set
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
Is this what you're looking for?
resource2 = CALCULATE ( MAX ( Data[ressource] ), FILTER ( ALLEXCEPT ( Data, Data[order no.] ), Data[phase] = 2010 ) )
4 Replies
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.
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.
