Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm trying to figure out a way to make this work;
Every day a person works he will have to do multiple calls. On each call the employee is promoted to choose if it's the first visit of the day, the visit in between or the last visit of the day. If it's the first visit of the day the employee is obligated to fill in their odometer gain and when it's the last visit of the day aswel.
So the data would look like this:
Date & Time | User | Begin odometer | End odometer gain |
03-06-2020 08:00 am | Tim | 14562 | null |
03-06-2020 09:00 am | Tim | null | null |
03-06-2020 11:00 am | Tim | null | null |
03-06-2020 12:00 am | Tim | null | 14685 |
The sum would be: 14685 - 14562 = 123. This works perfectly. But if the employee is not yet done with his day the current sum will give me an negative number as show in the picture below. Is there anway to figure this out?
Thanks in advance!
Wrong Good
KmVertrek minus KmAankomst =
IF (
NOT ISBLANK ( MAX('datalake answers'[KmVertrek]) ),
( MIN('datalake answers'[KmAankomst]) - MAX('datalake answers'[KmVertrek]) )
)
Solved! Go to Solution.
@Anonymous , try like
sumx(filter(summarize(Table, Table[Date & Time].Date, Table[User], "_1" ,sumx(Table, Table[Begin odometer]-Table[End odometer gain])),[_1]>0),[_1])
Group at day and user level and then check >0
@Anonymous , try like
sumx(filter(summarize(Table, Table[Date & Time].Date, Table[User], "_1" ,sumx(Table, Table[Begin odometer]-Table[End odometer gain])),[_1]>0),[_1])
Group at day and user level and then check >0
Hi Amit,
Thanks, i got it working using this formula:
KmVertrek minus KmAankomst =
sumx(filter(summarize('datalake calls', 'datalake calls'[ActualStart].[Date], 'datalake calls'[UserId], "_1" ,sumx('datalake answers', 'datalake answers'[KmAankomst] - 'datalake answers'[KmVertrek])),[_1]>0),[_1])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |