Get employees per site and per period

[ATR]

Hi everyone,

I want to get all employees sort by their place of work (site) and sort by their period. My final goal is to get their turnover (sales) and many other stats they have on their different place of work.

For example an employee can work on a place and move on another place and finally go back to the first like that: 

I have basically 3 tables responsible of this, Employee, Site and Site_History.

Fields of Employee: id, name, site_id (Site)  -one activate link with Site and one disabled link with Site_History (because of ambiguity)

Fields of Site: id, site_name  -one activate link with Site_History

Fields of Site_History: id, site_id (Site), employee_id, start_date, end_date

Actually, my matrix shows me employees in their current sites and gives me for example all their turnover of all their sites in their current site, which is false because if an employee has made turnover in paris it must not appear on lyon but stay in paris. If an employee have many sites, the matrix should show him as many he have site.

I hope you'll understand me and my problem.

Regards,

ATR

[DouweMeer]

You should create a calculation of the sales in the site history as a seperate column. Thus if sales was created within that time period, it counts for that site. 

Power BI doesn't auto split the sales figures based on those start and end. You have to tell Power BI what those columns are. So probably something like: 

calculate ( sum ( sales ) , filter ( sales , sales[date] >= site_history[start] && sales[date] <= site_history[end] ) )

Then use this column for sales figures instead of using the sales table. 

Hope this helps @ATR 

[ATR]

Like I thought, the formula does'nt work for the employees (repetition of the total of sales) neither sorted by date, even if i use userelationship to tell him what to do. 

Example of return: 

Any idea? @DouweMeer 

[DouweMeer]

🙂

Do you still have an active relationship between Site and Employee? If so, you should break that. Employee should have a relationship with Site History based on the Employee Id. And Site with Site History via the Site Id. 

Let me know @ATR if that doesn't work. Then I'll continue trying to create a dummy dataset to recreate your problem. 

[ATR]

It doesn't work, I tried to give you the dataset (simplified) :

Thank you a lot for your help @DouweMeer.

[DouweMeer]

I would say you can work in Attachment_Lines. You have to add 4 columns I would say from the perspective you're giving. 

Add 1 column with the values from Attachments.Creation_Att_Date. 

Just create 2 columns for Employee and Product. Get the values from Product via Attachment. Use fields Product.Id and Product.Employee_Id to fill the 2 calculated columns in Attachment_Lines. 

Then you can create a 4th calculated column to find the site based on the Attachment_Lines.Employee_Id the Site for the line. Use an if statement to get the right value. It depends on how Site_History is populated based on changes in Employee. Looking at the lack of a Employee.Start_Date, I would say something like

VAR a1 = selectcolumns ( filter ( site_history , [start_date] <= attachment_lines[creation_att_date] && [end_date] >= attachment_lines[creation_att_date] && [employee_id] = attachment_lines[employee_id] ) , "Stuff" , [Site_Id] )

VAR a2 = selecetcolumns ( filter ( emplyee , [employee_id] = attachment_lines[employee_id] ) , "Stuff" , [Site_Id] )

RETURN

if ( countx ( a1 , [stuff] ) > 0 , minx ( a1 , [site_id] ) , minx ( a2 , [site_id] )

In theory after this, you can tie you Site table to the 4th column instead to import the name for the visuals. Then you won't need to create a 5th column with the name of the site. Also, tie your date table to Attachment_Lines.Creation_Att_Date. 

Using Selectcolumns instead of calculate avoids the integrity checks of relationships, which improves performance as I'm noticing.

If it still is not working, perhaps hire me. Then you are allowed to share your dataset with me :). Otherwise, check with your product owner whether assistance from Microsoft is part of the contract and check up with them. We do have that in place and it is quite usefull as I receive some different approaches to my problems.

[ATR]

Thank you a lot again, my first test with your work doesn't work totally, I get the good sites for employees but its just the same as before, I get all the sales from all the sites for a employee. I'll work on it further and if i don't succeed I'll search another way to solve my problem.

Sadly, assisstance from Microsoft isn't a part of our contract, and sorry I can't hire you  @DouweMeer 

[DouweMeer]

You do use the fields Site and Employee from Attachment_Lines instead of their respective tables, right?

[ATR]

Yes I do, the only thing i change is this :

IF(COUNTX(a1; [stuff] ) > 0 ; MINX(a1; [Stuff]); MINX(a2; [Stuff]))

I don't understand why you put [site_id] here because its the value we search, on the other hand i just can't put [site_id] power bi say to me: "Cannot find name '[site_id]'.

[ATR · Accepted solution]

Ok, I modify a bit the formula, it didn't work because of an other table i didn't give you. ( I forgot a link in my dataset ) here the modified formula:

Site = 
VAR a1 = SELECTCOLUMNS( FILTER('site_history'; [start_date] <= 'attachment_line'[Attach_gen_date] && 
([end_date] >= 'attachment_line'[Attach_gen_date] || [end_date] = BLANK()) && 
'site_history'[employee_id] = 'attachment_line'[Employee_id]); "Stuff"; [site_id])

VAR a2 = SELECTCOLUMNS( FILTER('employee'; [id] = 'attachment_line'[Employee_id]); "Stuff" ; [site_id])

RETURN
IF(COUNTX(a1; [stuff] ) > 0 ; MINX(a1; [Stuff]); MINX(a2; [Stuff]))

Now I need to get others stats from others table, I'll work on it.

Thank you @DouweMeer for your great help, your time and your patience.

[ATR]

Another way to solve my problem is to do this: if site_history is empty for an employee I take the sales normally but if site_history isn't empty for another employee, I take the sales only for the last sale (his actual site).

I tried this too but i didn't succeed.

Yes, all the employees doesn't have an history of their sites, if a employee never move, his actual site is in Site and he doesn't have any Site_history.

Do you think it's possible? @DouweMeer 

[ATR]

Thanks for your reply, it helps me a bit but like I said I have many other stats, so I wondering if I could create a measure or calculated column which can do this for any data I want to show in my matrix. Like a column which uses exclusively these three tables. Maybe it's not possible..

I'll test this afternoon your suggestion thank you.

[DouweMeer]

Reminds me, add the employee Id in the filter as well, of course :).

But I understand your point. We have that issue as well. I just add calculated columns to my tables extracting information from other tables to get all in 1 place. What might happen is that you do a lot of calculation over your relationships. When the report becomes to heavy, you can think of using selectcolumns + filter + minx instead of calculate + filter.