Duplicated time values - How to prevent double counting?

[L_B]

Hi,

I've merged (left outer) 2 Fact_Tables based on a specific column (i.e. employee codenumber plus date).

- Table A:  All values regarding salary, group of employee, kind of operation, etc.

- Table B: retrieve only values to his/her checking in/out time

Now, I want to calculate his/her time on quay (so the difference between the check-out & check-in ==> DATEDIFF).

Sometimes, an employee changes to another kind of operation which generates an extra line for the same employee in Table A (I cannot remove this information because this is needed for cost calculations). 

This generates a copy of his/her checking in/out time which will create/generate double countings for his/her time on quay. 

How can I solve this problem?

thanks in advance,

L_B

[L_B]

@Mariusz ,

The goal is to examine the effective worked time on quay so the difference between time in & time out.

The issue is about the lines in yellow. I guess this creates a sort of 'double counts' if you create a new DAX-measure (i.e. DATEDIFF)? 

grtz,

L_B 

[v-juanli-msft]

Hi @L_B 

If  the answer from Mariusz slove your problem, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please let us know the relationship between two tables and what is the key column to be related between them.

Best Regards

Maggie

[L_B]

Hi, @v-juanli-msft

@Mariusz 

I guess I was not clear enough in my explanation. The columns are merged based on the column "Kolommen samen" and this not an issue or something else.

First of all, I want to calculate the difference between DateTimeIn & DateTimeOut. This could be done by using the DATEDIFF-function.

However, when the employee is used during his/her shift on another location (see yellow lines below on the snapshot). His/her line appears twice in the data splitted by the hours worked on the specific location. Therefore, when I am using a normal DATEDIFF-function this will calculate it twice for the same employee. 

grtz,

L_B

[Mariusz · Accepted solution]

Hi @L_B 

You can create a measure like below.

Measure = 
SUMX(
    GROUPBY( 'Table', 'Table'[ID], 'Table'[DateTimeIn], 'Table'[DateTimeOut] ),
    DATEDIFF( 'Table'[DateTimeIn], 'Table'[DateTimeOut] , MINUTE )
)

Or split your fact table so DateTimeIn and DateTimeOut are separated and not duplicating.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @L_B 

Sure, can you create a data sample that represents your scenario and also includes the expected result with an explanation.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn