DAX or M for efficiency - Staff Database

[Anonymous]

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

[CNENFRNL]

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.

Expanded tables in DAX - SQLBI  

[Anonymous]

Thanks CNENFRNL, would that be quicker than the proposed solution of using a couple of calculated columns using the DAX MAX function?  I can't quite see how it would help me because of the multiple rate problem I'm trying to address - it seems to essentially be flattening the table.

[Greg_Deckler]

@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.

[Anonymous · Accepted solution]

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?

[Anonymous]

Hey Greg - stupid question, how do you change the direction to both? Not something I've come across.  A quick google isn't coming up, so I obviously don't know what keyword to search for!

[Anonymous]

ah, looking at this, I think this is PowerBI only, not excel specific, so can't seem to do that 🙂 

[Greg_Deckler]

@Anonymous Ah, shoot, yeah I don't do much in Excel. In Desktop you doubleclick the relationship and switch it.