Forum Discussion
Headcount relationships
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!
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