Modeling issue

[Jensej]

Hey there! 

I have a question about Modeling. Iv'e read and seen a lot of tutorial about modeling but it's always the same example of a model with a Sales factable. 

Where i work we have clients with special needs that lives in our facilties in groups (each floor is a group).

I need to make a Power BI Report where i can show some stats about the Clients and Employees per Group and also in Total. 

For example how many clients and which gender did  we have on Group A  Oktober 2018. 

Another example could be how many employees did we have at the same time and how much % was people working in Total. 

I have 3 SQL Views (Employees, Clients, Monthly Use) and 1 Date Table at the moment.  Monthly use table holds information about articles that have been used from our clients. 

How do i model this correct? As i can see i have no real fact table? 

Is it necessary to make a fact table that stores like only Emp_Id, Client_Id?

Another problem is that i have two date fields in both employee and clients table. emp_Hire_date, emp_Resign_date and cl_entering_date and cl_exit_date. 

I can only have one active relation to the Date table. Should i make both inactive so that i can use both in my report or how does it work?

I have no sample data to share so i hope you understand what im searching for and could give me some tip. 

In my case i need to 

[MFelix]

Hi @Jensej ,

One thing that I'm not getting in the information you are giving is how are the 3 tables connected with the groups? Taking into account that you say you need the information by the groups you need to have that relationship between your table ate least the employees and the clients, these depends on how you have your data and but believe is a key part to have eveything working properly.

Concerning the secon question about the several dates you have two options to have one active relationship and then inactive relationships and make use of USERELATIONSHIP formula to activate the inactive relationship when you need them, another is to have no relationship and then make use of FILTER function to get all the values working.

Both options have advantages and disavantages so depends on what you want to see.

Hope this helps to get some light on your issue if you need further assistance please tell me.

[Jensej]

Hi @MFelix !

Thanks for your reply. 

Regarding your question about the groups.

In the view for the clients there is a column which says what group they are in with following values (Group A, Group B, Group C, Group D, Group E) 

In the view for the Employees there is also a column that tells where they work. (Amsterdam,Boston,Chicago,Domodossola)

A = Amsterdam, B = Boston, C = Chicago and D/E = Domodossola.

Notice that Employees that work for Group D or E  are togheter.

About the second reply. I want a slicer with a date on every page on the report where they can choose a date and then it should only return the clients/employees that was in the company at that date. (Filtering on hire and resign date and client entry and exit date). 

What's best? One active connection or two unactive?