calculate on several filters

[mettecassoe]

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  

Less than 101 km = COUNTROWS(

FILTER(ReportXls; ReportXls[Km] > 100))

is not sufficient as it does not take into account that a trip can include several rows.

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.

[V-pazhen-msft]

@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.

[amitchandak]

@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.

[mettecassoe]

@amitchandak Thanks for replying!

I have tried eg.

Between 101 and 200 km = CALCULATE(

DISTINCTCOUNT(ReportXls[Indeks]);

FILTER(ReportXls;

ReportXls[Km] > 100

&& ReportXls[Km] < 201

))

This would work if one row was = one trip, but in some cases several rows = one trip. I can only identify a trip by finding out the earlies start time on a date for a car, identify the start adress and then find end adress = start adress.

A trip can also be over several days, eg if someone drove somewhere and stayed at a hotel and then drove back the next day.

[Greg_Deckler]

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

[mettecassoe]

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.