Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My data is registrations of trips by several cars over the last 6 months.
I would like to investigate the lenght (km) of each trip to find out how many trips are less than 100 km, between 100 - 200 km and above 200 km.
The cars have a GPS tracker installed that registers start adress and start time/date as well as endadress and end time/date, one registration per row. The car only needs to be standing still for a short while for the end time to be registered, therefore a trip can consist of several rows.
My columns are as following:
Car - Date - Adress start - Adress end - start time/date - end time/date - km
A simple
Im thinking I need something like:
Sort on car
Identify a date for that car
Find the earlies time on that date for that car
Identify Adress start
Identify where Adress stop = Adress stop
Calculate total km for rows
Any help with this is very much appreciated! Thanks in advance.
@mettecassoe
1. You can use the filter panel, just drag the the columns needs to be filtered into the visual filter.
2. For dax you can use And(&&) , Or(||) to add all those filters in the formula something like: calculate(countrows(ReportXls),filter(ReportXls,ReportXls[Km] > 100 && ReportXls[date]< Date(2020,3,1))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mettecassoe ,You can use && , and, || ,or, to add additional conditions. You can use the slicers to filter on the fly.
Not sure I got it.
@amitchandak Thanks for replying!
I have tried eg.
Sample data would be tremendously helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thank you Greg, for the link on how to post correct.
Here is my sample data:
Car | Date | Adress start | Adress end | Start time date | end time date | km |
Nissan Quashqai | Jan 31 2020 | Adress1 | Adress3 | 31-01-2020 11:50:10 | 31-01-2020 11:54:20 | 1 |
Nissan Quashqai | Feb 3 2020 | Adress3 | Adress1 | 03-02-2020 09:51:51 | 03-02-2020 09:55:10 | 1 |
Nissan Note | Jan 31 2020 | Adress4 | Adress5 | 31-01-2020 14:16:46 | 31-01-2020 14:42:35 | 7 |
Nissan Note | Jan 31 2020 | Adress2 | Adress7 | 31-01-2020 10:20:23 | 31-01-2020 10:23:52 | 1 |
Nissan Note | Jan 31 2020 | Adress2 | Adress4 | 31-01-2020 13:24:29 | 31-01-2020 13:48:48 | 6 |
Nissan Note | Feb 5 2020 | Adress6 | Adress2 | 05-02-2020 11:05:28 | 05-02-2020 11:07:39 | 0 |
Nissan Note | Jan 31 2020 | Adress7 | Adress2 | 31-01-2020 10:50:50 | 31-01-2020 10:52:20 | 0 |
Nissan Note | Feb 5 2020 | Adress5 | Adress6 | 05-02-2020 10:35:40 | 05-02-2020 10:40:21 | 1 |
As you can see the Quashqai left the home adress1 on Jan 31 and did not return until Feb 3 - total trip 2 km
The Note left the home adress2 Jan 31, had a small trip of 1 km and then had another trip starting Jan 31 and returned Feb 5, had several stops in between - total trip 14 km
Hope this gives more information to help me.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |