How can I calculate cumulative cost of Pay in the model attached? I’m trying to avoid many-many relationship with the fact table Hours_Worked.
The rates Full-Time and Part-Time are not necessarily related by any ratio i.e I can’t rely on a factor to multiply to but rather must rely on lookup table.
Week 2 Accelerator course explains benefits/importance of star-schema vs snow-flake. I don’t understand how I should relate Rates (Full-time and Part-time) to Hours_Worked table.
You were actually quite close here. If this is the full extent of your model, then keeping Rate Data as a disconnected helper table accessed via our expanded lookup measure created in the previous post is a fine way to go.
All you need to do was mark your Dates table as a date table, then create the proper context in the matrix visual. You do have some choices as to how best to display cumulative total across two variables. You can either do cumulative only on date ( Cumulative Pay 1 measure) or do a more complex nested cumulative on date and staff id ( Cumulative Pay 3 measure ).
I’ve included both measures in the solution file attached below, so you can just choose the one you like best (I think I like the latter).
Thanks Brian,
Just a question regarding the lookup rates - what is your recommendation on splitting the rates table into Full-time and Part-time rate tables both pointing to the fact table? I’m wondering if this might simplify the relationships and dax? My preference is usually to steer away from lookup dax if possible.
“what is your recommendation on splitting the rates table into Full-time and Part-time rate tables both pointing to the fact table?”
If you’re trying to avoid the lookup DAX and simplify the DAX overall, here’s the way I would do it - I would restructure the Rates table by transitioning from an active date range (rateactivationfrom to rateexpired_on) to a straight-up weekly granularity. That way, you could do a direct merge on Category, Staff ID and WeekEndingFriday date and just pull the proper rate into each row of the fact table (Merge is the PQ equivalent of LOOKUPVALUE). If you didn’t care about increasing the cardinality of your fact table, you could also compute the Pay value in PQ, further simplifying your DAX.
