Return a subset of assets based on related criteria

[robertarm]

Hi guys,
I don't even know if this is a power pivot question or something able to be accomplished just in excel but I haven't been able to find a solution so far.
I have a list of work orders with all relevant info (standard job, comments, work order number, plant number etc.) What I want to be able to do is filter for all of the assets which have a workorder with a specific standard job, but then return all work orders attached to these assets not just the the ones with this job code.
All of the info is in a flat table at the moment in a single excel sheet (extracted from other databases into .xls format).
The standard job I want to filter on will be different each time (There is a finite number of options, maybe 40), but I would like the sheet to be able to be easily manipulated by people who are not even close to being excel power users.
Ideally I would like it to return a pivot table listing all of the assets, the various standard jobs so these can be summed, but most importantly be able to be expanded from the pivot table to return the full list of filtered results so they can be used to issue work and ensure when someone goes to an asset every job is issued efficiently.

Excel 2010

	A	B	C	D	E	F	G
1	Work Group	Work Order No	Plant No	Work Order Desc	Standard Job No	Standard Job Desc	Comments
2	CHOXPM1	04316305	DS00501	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	insulation on drop out fuses perished
3	CHOXPM1	04354555	DS00547	Oil Stain	1OILST	Oil Stain	OIL STAIN ON BOTTEM EDGE OF SUB
4	CHOXPM1	04442514	DS00566	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	roadside expulsion fuse deteriorated
5	CHOXPM1	04401307	DS00593	Oil Leak	1OILLE	Oil Leak	oil leak from drain
6	CHOXPM1	03654611	DS00629	Oil Leak	1OILLE	Oil Leak	chek oil leack at tap and at top of sub
7	CHOXPM1	04542591	DS00658	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	dof fuses perrished
8	CHOXPM1	04691703	DS00727	3x expulsion fuses deteriorated	1DOFSW	Replace DOF Assembly
9	CHOXPM1	03642707	DS00756	Vermin	1VERMN	Vermin	birds nesting
10	CHOXPM1	03958717	DS00760	Oil Leak	1OILLE	Oil Leak	repair oil leak
11	CHOXPM1	04448138	DS00861	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	expulsion fuses x 2 deteriorated
12	CHOXPM1	03855113	DS01353	Oil Stain	1OILST	Oil Stain	oil stain at drain plug
13	CHOXPM1	03855114	DS01353	Replace Surge Diverter	1SURGE	Replace Surge Diverter	old red insulators require replaceing
14	CHOXPM1	04318027	DS01543	Repair/Replace HV Hardware	1HVHAR	Repair/Replace HV Hardware	TYPE: Distribution Substation UG , ASSET NO: 1543 , DESCRIPTION: UG Dist Sub 1543, Padmount HV Switchgear , LOCATION: Roony Ave, Abbotsbury 2176 Switchgear moves excessively when operated-requires resecuring to plinth Passed on to Brendan Leshone
15	CHOXPM1	04518812	DS01714	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	replace degraded drop out fuses
16	CHOXPM1	04528990	DS01714	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	replace degraded drop out fuses
17	CHOXPM1	03688963	DS01873	Oil Leak	1OILLE	Oil Leak	oil leak Contestable Works advised sub will be replaced under URS project in June 2015
18	CHOXPM1	03698639	DS01879	Oil Leak	1OILLE	Oil Leak	oil leak on sub
19	CHOXPM1	03932426	DS01879	REPLACE HV CROSSARM	1HVARM	Replace HV Crossarm
20	CHOXPM1	04518801	DS01946	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	replace degrade drop out fuses
21	CHOXPM1	03620714	DS02275	Oil Leak	1OILLE	Oil Leak	OIL LEAK FROM SUB 2275
22	CHOXPM1	03934712	DS02275	REPLACE HV CROSSARM	1HVARM	Replace HV Crossarm
23	CHOXPM1	03547607	DS02403	Oil Leak	1OILLE	Oil Leak	oil stain on sub
24	CHOXPM1	03931960	DS02403	REPLACE TRANSFORMER	1TXREP	Replace Transformer
25	CHOXPM1	03932388	DS02403	REPLACE HV CROSSARM	1HVARM	Replace HV Crossarm
26	CHOXPM1	04012529	DS02458	Repair/Replace HV Hardware	1HVHAR	Repair/Replace HV Hardware	replace drop out fuses
27	CHOXPM1	03769406	DS02750	Replace Surge Diverter	1SURGE	Replace Surge Diverter	old style brown serg diverter
28	CHOXPM1	03183383	DS02929	Repair/Replace ABS Arcing Horn	1ABSAH	Repair/Replace ABS Arcing Horn	arching horn needs removing
29	CHOXPM1	04316310	DS03042	Replace DOF Assembly	1DOFSW	Replace DOF Assembly	drop out fuses insulation frayed
30	CHOXPM1	03596589	DS03325	Oil Leak	1OILLE	Oil Leak	check oil leak
31	CHOXPM1	03931937	DS03325	REPLACE TRANSFORMER	1TXREP	Replace Transformer
32	CHOXPM1	03659010	DS03338	Oil Leak	1OILLE	Oil Leak	light oil leak
33	CHOXPM1	03932406	DS03338	REPLACE BROKEN MDI UNIT	1MDIUN	Replace Broken MDI Unit
34	CHOXPM1	03659026	DS03341	Repair/Replace LV Hardware	1LVHAR	Repair/Replace LV Hardware	meter box wire hanging out of sub
35	CHOXPM1	03748162	DS03581	Replace HV Insulator	1HVINS	Replace HV Insulator	replace chipped insulator

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I'm running Excel 2010 on Windows 7.

 

[LloydFinancials]

Will you provide an example given your data here?

 

[robertarm]

Sure,
If I filter a pivot table for standard job 1HVARM. I get 3 results

Excel 2010

	A	B	C
1	Standard Job No	1HVARM
2
3
4	Plant No	Work Order No	Work Order Desc
5	DS01879	03932426	REPLACE HV CROSSARM
6	DS02275	03934712	REPLACE HV CROSSARM
7	DS02403	03932388	REPLACE HV CROSSARM
8	Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

However I want to see all wokorders for the 3 assets which have a workorder with this standard job (I have applied the filter to the assets below for this example, but in my full sheet there are 40,000 WO's and 30,000 assets so it isn't a realistic option this way).

Excel 2010

	A	B	C
3
4	Plant No	Work Order No	Work Order Desc
5	DS01879	03698639	Oil Leak
6		03932426	REPLACE HV CROSSARM
7	DS02275	03620714	Oil Leak
8		03934712	REPLACE HV CROSSARM
9	DS02403	03547607	Oil Leak
10		03931960	REPLACE TRANSFORMER
11		03932388	REPLACE HV CROSSARM
12	Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

 

[Ozeroth]

Interesting problem!

The core of the problem is that you want to select a Standard Job No, and filter your PivotTable not just to those Standard Job Nos but to all Plant Nos that have a work order with that Standard Job No. This means a filter directly on the Standard Job No column won’t work.

I’ve uploaded a file with a sample solution (Excel 2010 PowerPivot):
https://drive.google.com/file/d/0B9pNjpDQKy_LM09KMjdaM2N4N1k/view?usp=sharing

My method is to create a second table containing Standard Job Nos, relate your main table to it using an inactive relationship, use a slicer on this lookup table to select a the Standard Job No, then use DAX to create a measure that is used to filter Plant No.

The end result is that the user just selects a Standard Job No on a slicer and the PivotTable filters as you’ve described.

These are the steps I followed:

1. Create a “Standard Job Number Selection” lookup table that contains distinct Standard Job Nos (and descriptions if you want). I have done this manually but you should use Power Query or similar.
2. Add a relationship from your main table’s “Standard Job No” column to the Standard Job No Selection column in the new table.
3. Mark this relationship as inactive, so that it doesn’t automatically filter your PivotTable.
4. Create a PivotTable with the row labels you require, including at least Plant No. I’ve included Plant No, Work Order No & Work Order Desc as per your example.
5. Add a slicer for the “Standard Job No Selection” column from the “Standard Job Number Selection” table.
   At this stage, the PivotTable will still be unfiltered, showing all work orders.
6. Create a measure which will act as a flag indicating whether to include the current plant. This measure will return 1 only if there is a single plant present in the filter context and that plant has at least one work order with the selected Standard Job No. The red CALCULATETABLE section creates the list of “valid” plants by using the inactive relationship to filter the main table. The VALUES ( Data[Plant No] ) in green gives us the list of plants in the current context (which is just a single plant at a time in the PivotTable I have set up). The IF(HASONEVALUE(...)) will be evaluated in the context of the intersection of the red and the green, which will be the current plant only if it is “valid”, in which case the measure will return 1, otherwise blank.
   

   [COLOR=#000000][B]Flag - Single Plant has Selected Standard Job[/B] :=
   CALCULATE (
       IF ( HASONEVALUE ( Data[Plant No] ), 1 ),[/COLOR]
       [COLOR=#008000]VALUES ( Data[Plant No] ),[/COLOR]
       [COLOR=#ff0000]CALCULATETABLE (
           VALUES ( Data[Plant No] ),
           ALL ( Data ),
           USERELATIONSHIP ( Data[Standard Job No], StandardJobNumberSelection[Standard Job No Selection] )
       )[/COLOR]
   )

   <strike></strike>
7. In the PivotTable, filter the Plant No column (drop-down or right-click Filter) by selecting Value Filter: “Flag – Single Plant has Selected Standard Job” equals 1.
8. Now if you select one (or more) Standard Job Nos on the slicer, the PivotTable will be filtered appropriately.
9. Note: In case you later want to add a separate filter on Plant No (e.g. a slicer to further limit the plants you are looking at), go into PivotTable options and enable “Multiple Filters per Field”.

 

[ImkeF]

Interesting problem!

Hi Ozeroth,
these were my thoughts as well. Tried to find a Power Query solution for it and failed.

Now reading your Power Pivot solution on it was a real eye opener: If I could only get my head around these context things in DAX (they still are a moving target for me): What an elegant solution to come up with!
This really motivated me to get back into DAX.

But on the other hand, the description you’ve provided ordered some thoughts and made the Power Query solution appear:

let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Merge = Table.NestedJoin(Source,{"Plant No"},Source,{"Plant No"},"NewColumn",JoinKind.Inner),
Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Standard Job No", "Standard Job Desc"}, {"Standard Job No.1", "Standard Job Desc.1"})
in
Expand

So you simply load the table and join it with itself on Plant No: The column whose matches on shall be returned. Then expand on the Job No’s and descriptions to show: Done.

... thought that this will make you smile

Thank you!

LinkToFile

 

[Ozeroth]

Hi Imke,
Glad to have motivated you back to DAX!
I like the simplicity of your "join the table with itself" method - not something I would have thought of

robertarm - I would be interested in how large a table you end up with following Imke's method on your full dataset. Is either method preferable in your situation?

All the best!

 

[robertarm]

Ozeroth and Imke,
thanks for your feedback it is greatly appreciated.
I was starting to think I would have to get creative with VBA to push out a solution for me.

I have so far implemented Ozeroth's solution on my full sized table I currently have which is only 8000 records and it all works fine. My system does lag for 5 seconds or so with some operations when you play with the table, but overall not something I am hugely concerned with. I will wait and see how it goes on the full list of 40,000 if I ever need to run it on that table.

I don't fully understand the solution but can decipher most of it (I only got powerpivot recently so I could get distinct count in a few tables I needed and have been having a bit of a play since then) from other reading I was doing trying to find a solution to this myself. I found nothing which brought me close to this, but it is a hard problem to find the right search term for,

ImkeF, thanks for your input too. Unfortunately I can't access your file because it is asking me for a username and password to a live account.
I actually put powerquery on also hoping there would be an elegant out of the box solution for what I wanted but there was nothing and it appeared to be very foreign to me and wasn't intuitive to me at all.
What does that query actually do and how do you do the final sorting etc?

At the end of the day I need this to produce a pivot table/table for people far less excel savvy than myself (and primates compared to what you 2 have proven) who really are only basic excel users so simplifying the final product for them to chop and change their filters for different standard job numbers is the main objective for me.
Ultimately the data for this will be a daily update using cognos from our corporate database. I am currently getting this dumped in xls for ease of use but will have to find a way for this filterable table to look up that info.

Again, thank you very much for your input in getting me to this point where I have something which works.

If you have some pointers on which solution would be optimal going forward on the additional info please let me know, but what I have already does what I need.

 

[robertarm]

I have just found one limitation.
I can't drill through the table to export all of the details on every WO to a new sheet as you would in a normal pivot table.

Doing that just exports everything unfiltered.
Is there a way to handle this?
Is Imke's solution better to handle this? but I still have the questions above around usability for the general public.

 

[ImkeF]

Sorry about the link, wasn't aware of the limitation.
Try this one please: http://www.thebiccountant.com/download/456/

For the drilldown you need a measure in your pivottable. I've included it in my solution. (Also the Grand Total for all of them)

I'm expecting the filtering on the JobNo to be very fast, but have not tested it - so I would very much welcome your feedback on this

Loading the updated Input data into the model should take longer than Ozeroths, because the Power Query calculation has to be done as well, but once this is done - at least in theory - it should be very fast.