Grouped list for sequence operations

[Gabsanche]

Hi Everyone,

I am working with a large list of data representing pending operations that will take place in different work center which are associated with an average time of operation. I want to use that data to display a graph of the future operations that will affect critical work centers. (I have around 10 critical work centers). So far I have been able to transform the data and generate the desired table but it was done in a very manual query that I need to duplicate for each additional critical work centers. I am looking for a more efficient way, where I could use a list of criticals work centers instead of having to create a new duplicated querry for each of them. 

These are the columns I'm working with:

Work Orders: Is the top hiearchical variable and is composed of multiple operations

Operations: Are chronological numbered operations which are to be applied to the work order

WC: Is the work center ID where the operation is to take place

Waiting Time: Is the average time that should be spent, in days, in that work center.

The logicial steps I am using are:

1. Choose 1 critical Work Center (WC), exemple "P22"

2. Eliminate any work orders that do not contain operation associated with work station "P22"

3. For every work orders, eliminate the operations taking place after "P22"

4. For every work orders, Sum the "Waiting Time" of remaining operation

5. Group the work orders with a "count" measure on the sum of time 

My resulting table looks like this : 

In this exemple, I have selected work center "P22" and the resulting table shows that :

- 11 work orders are to take place in 0 days (basically today)

- 54 work orders are moving to "P22" in 2 days

- etc...

Any help and ideas will be much appreciated.

Thanks,

Gabriel

[Icey]

Hi @Gabsanche ,

Not the same, but this post may provide some help for you: ETL Through Multiple Pages of a Website in Power B... - Microsoft Power BI Community.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[StefanoGrimaldi]

please share dummy data of it, and specify 2 critical workspace to use as test for a better and quickier answer. its better if you always share dummy data in this type of question for a quickier and better respond. 

but without it you could try making a second query that bring from a external source witch are the critical selected, merge to the main query, and with some grouping and filtering should be on the way to make it them all in one table without needing of extra query.