Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
@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
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
Hi, @v-juanli-msft
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
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.
User | Count |
---|---|
8 | |
7 | |
5 | |
5 | |
4 |
User | Count |
---|---|
14 | |
13 | |
8 | |
6 | |
6 |