Data relationship modelling issue

[Arlind_Selimaj]

Hi all,

I have a date query which is listed on the top, and i have two fact tables on salaries. The first one indicates shift hours per employees, and the one on the bottom indicates total salary per employee (Wages complete). The tables are connected by employee name.

The issue is that when i want to calculate the exact salary per hour (which requires total wage from the bottom table to be divided by shift hours at the other table), the total wage from the bottom table can not be filtered by Date but is showing all wages of all employees. I presume that is because the Wages complete is not connected the Date table. Problem is, when i try to connect the Date from Wages complete to Dates table, it says it will introduce ambiguity between Wages by shift and Date.



How do i model the relationships correctly so that both wage tables can be filtered by Date?

[v-xuxinyi-msft]

Hi @Arlind_Selimaj 

Can you provide example data for the two tables you used and the results you expect? 

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

We can better understand the problem and help you. Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Community Support Team _Yuliax

[Daniel29195]

@Arlind_Selimaj 

when you want to work with (hours, min, sec ) , 

i would recommend you to create w time dimesion . 

then in your fact table, split your datetime column into 2 columns  : 

date , time 

then link dimdate to date , and dimtime to time  

this would ease your work ,

also , it would make  your model more optmized  which in turn would increase performance while decreasing your model size .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[speedramps]

Dont just import data as it is storted on source systems.

Model the data so you can report it easily.

Use Power Query to model your data into start schema like this ...
Learn about star schema modelling here 

Star schemas