Forum Discussion
DAX or M for efficiency - Staff Database
Hi!
I have a timesheet database I'm trying to make more efficient. A cut down version is attached.
"staff" will contain information about the staff member (just has a resource ID no at the moment for this cut down trial)
"staff rates" has a list of all the staff. A grade may change over time as they get more experienced (this additional item which was originally in Staff as a single entry is what is complicating matters!)
"client rates" is what we bill out
"timesheet" is just a list of timesheet entries.
If there's a couple of hundred people booking, the timesheet extract will be very full with hundreds/thousands of entries. At present we do not deal with grade changes, but now need to take this into account (what was simple is now vastly more complicated!)
Previously we've imported the rate into the timesheets at import, the import takes a very long time and I suspect this is because of it, but I'm not sure how I can lookup the data using a measure with this odd layout which I'd assume would be much quicker for this sort of thing.
https://www.dropbox.com/s/ekrl2nqgmeum59g/example%20timesheet.xlsx?dl=0
So I've used MAX for the lookup to get the value as a helper column and then the rate from there. I realised the duff data I put in for the example made no logical sense, so adjusted some of the timesheet and grade dates to occur in the past.
Is this just the easiest way of doing it, or is there a more efficient way?
7 Replies
Anonymous Well you could use LOOKUPVALUE or MAXX(FILTER(...),...). Or, if you changed a couple of your relationships to Both you would have a filter pathway between timesheets and rates tables.
So I've used MAX for the lookup to get the value as a helper column and then the rate from there. I realised the duff data I put in for the example made no logical sense, so adjusted some of the timesheet and grade dates to occur in the past.
Is this just the easiest way of doing it, or is there a more efficient way?
Since bi-directional filtering isn't available for you in Excel, you may want to resort to expanded table for invert filter propagation from * to 1 end.
