Forum Discussion

L_B's avatar
L_B
Frequent Visitor
6 years ago
Solved

Duplicated time values - How to prevent double counting?

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

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

  • L_B's avatar
    L_B
    Frequent Visitor
    6 years ago

    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 

     

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      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

       

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      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's avatar
        L_B
        Frequent Visitor
        6 years ago

        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