Headcount relationships

[rosscortb]

Hi 

I'm trying to create a dashboard on Headcount, Absent, Leavers, Overtime etc. I am using the table Level34 sector as my filter. I want a table visual listing the employee names, on each page so that when clicking a chart etc, it will filter down to see who the employees are.

I have a created a reference headcount2 table and removed duplicates and tried to create relationships which has worked out  up to the point but  I try to add level 34 to absence it doesnt like that? Am I going about this the correct way in terms of relationships?

Thanks

Ross

[SteveCampbell]

Without seeing the data in detail, I would reccomend reading this:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

I always advise to use star schema. As much as possible, make sure all filters are from dimension tables, while any expression (such as a SUM or AVERAGE of something) is from the fact tables. This also helps to not have missing fields, or handle blanks correctly. This holds true on graph axis too. For example, a bar graph axis may have “Product”, while the values might be “Sum of Sales”. Each bar you see, Power BI is applying a filter to that specific product, then calculating the sum of sales, after this filter has been applied and passed down. This means the axis on graphs are actually filters. Therefore, your axis should be from Dimension tables, while the values from the fact. Same with CALCULATE statement- try to have the expression from the fact, and filters in the dimensions. Even if you have a small dataset, Star schema can help with graph creation and drastically improve the ease of writing DAX.

Feel free to reach out if you have more questions.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can! 

[rosscortb]

Hi Steve, 

Thanks for your response. Based on your reply I changed my data. I created Dim table like sector, cost centre, org unit etc and then I have a fact table{Headcount] with employees details month on month. I also created another Dim table of every employee  and create a Uniq ID and joined the Tables together, Employee Uniq ID to Headcount Unq ID, that's seem to work fine.

I've now added an Overtime Table and tried to do the same thing, however, I can't as there is an issue with Overtime Sector ID linked to Sector ID. If I delete this I can't filter by sector. What am I doing wrong please? Cheers Ross