Hi All
I currently have 3 tables (date, employees and sales). I have started to build this but I am stuck with how to link employees and sales. I am guessing I need a linkage table?
In the employees table, the employee can appear more than once as they are promoted (with start and end date). I would like to get the sales by job grade by month.
SalesDashboard.pbix (66.9 KB)
Many thanks!
Hi @swong12,
This is known as a slowly changing dimension for information on the different ways you can deal with them please see here and here .
I hope this is helpful
I’m assumming you want to create a join where the data returns if equal. Picture above shows where to go.
SalesDashboard.pbix (68.0 KB)
Hi @lbarron20 I’d check out the links that Melissa posted on Slowly Changing Dimension tables. You would want to have a 1-many relationship from the employee table to the sales table. You need to create a unique key in in the employee table. Would likely be a combination of their name/unique ID combined with the date they started a new role. You’d then mimic this key in the sales table with the name/unique ID and date of sales. This way you’d be able to match every sale to the person and which role they’re in at a specific date.
I may have misnamed some of the column/table names as I’m writing this on my phone but hopefully you get the concept.
DJ
Thank all, will read up on the above links as I wasn’t sure how to create the one-to-many relationship.
If I get chance I will try to do so too but I’m travelling today so if someone else steps in then I’ll be happy you get assistance.
This video from Curbal would be a great way to extend the employee table and help you create a unique employee key over the time they were in each role.
Thanks, will watch this video and investigate how to apply on my example!
Thanks @DavieJoe
I have followed your advice on the unique key and the video of creating the list across the 2 dates.
Have attached the file for reference if anyone else is interested.
SalesDashboard.pbix (18.4 MB)
That’s fantastic to hear, a simple but really powerful M Code technique!
