

June 24, 2016

Hi, hope someone can help. I've used Excel for years, but only very recently got into Power Query, PowerPivot and Power View.
I'm working with a system DB that holds logged event records and these are held in a single table. Actions taken on those records are stored in a separate table. Each action contains a shared reference back to the main record table, so the actions can be used as a lookup. To measure the time a record has been open but also in different states, based on it's respective actions, I need to query the records table multiple times to get when the record was moved into a state and again moved out, and every time it happened. The process of moving into and moving out of a state uses a specific action type for each, and the actions are dated.
What I have done is create extracts via Power Query, so only working with the records I need and the respective actions. What I think I need to do is use PowerPivot to use the actions query table as a lookup and consolidate the action data into a single calculation. What I do not have clue about is any DAX function that can carry out the consolidation for me.
i.e. Record A has been put into state x 3 times, and the action table has 3 actions for moving from state x to state z, and 3 for moving out of state z and into state x. I need to work out how much time Record A has spent in each of the 2 states across its lifecycle.
I would love to share some sample data but security concerns will not allow me to do so. I can answer non-specific questions about the data if necessary.
Anyone have any suggestions for me?


June 24, 2016

OK, to update, I've worked out how to consolidate the dates in the second table, by modifying some of the values to a negative (based on specific rules) so the net sum of these values gives the actual time expired. What I cannot workout is how to get a sum lookup to work, so that when I go back tot eh master table I can sum all of the adjusted values for each master record. I've tried using a SUM calculation in the master Power Pivot table but it simply sums the whole column, not the specific values applicable to each master record. Anyone got any ideas at all?


June 24, 2016

Catalin,
The attached is pretty much the best I can do without breaching any confidentiality that applies. The real data and system are sensitive, so I cannot extract a 'real' sample, but I think the attached gives you the gist of the issue.
I need to calculate the sum of the time expired within the actions. I've sued the adjusted values to get to a positive value for on hold and negative value for off hold, with the aim of simply summarising the actions for each ticket. What I need in column E is the DAX (or normal formula for that matter) that looks up the ticket ID and summarises all action entries for that ticket.
I should point out that on the actions table, they are recorded in date order, though I can of course apply a custom sort in the Power Query if sort order is a factor.
Si
Most Users Ever Online: 57
Currently Online:
16 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas