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?
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?
Hi Anonemouse,
You have to upload a sample file with dummy data, otherwise it will be very difficult to visualize your data structure.
Try to create a sample file with your data structure, I hope there are no security concerns at home 🙂
Catalin
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
There is no attachment, please upload again.
After you select a file, press the "Start Upload" button, otherwise the selected file will not be uploaded.
Oops, my bad.
Much better, now it's clear what you need.
You have at least 2 options: you can group the Actions with Power Query by ID, adding the Adjusted Time column, or you can use the formula provided in the attached file.
Catalin
Thanks Catalin, I didn't realise SUMPRODUCT would work that way. It's kind of working but producing wrong values, based on sample testing. I need to dig through the syntax to see where I'm going wrong. I suspect the issue lies with the nature of the data, not the formula.
Well, at least in your sample file SUMPRODUCT is returning the expected results you provided, no idea what is in the real data.
Let us know if you managed to make it work.
Cheers,
Catalin
