topic Re: Calculate Dynamically Change Duration in DAX Commands and Tips

Calculate Dynamically Change Duration

Eesa — Tue, 21 Dec 2021 10:10:35 GMT

Hi,

I have a FLIGHT table below

I want to use a slicer in my dashbord and when I adjust the slicer between any date/time, I want to get the total time in air.

To imagine this I created a simple illustration below. The red dashed line is my slicer to pick a start date/time and end date/time. Each blue line line represent a flight and its actual duration.

Now if I want to adjust my slicer between 1/1/2021 19:00 and 1/2/2021 5:00, I must get a total time in air of 25 hrs.
I have a calendar table that has a relation with FLIGHT table and in which i will adjust my slicer from.

So, how to do that in Power BI?

Thank you for your help

Re: Calculate Dynamically Change Duration

amitchandak — Tue, 21 Dec 2021 10:45:32 GMT

Eesa , Assume you have independent date and time slicer (not joined with table)

measure =
var _sdate = minx(allselected('Date'), 'Date'[Date])
var _edate = Maxx(allselected('Date'), 'Date'[Date])
var _stime = minx(allselected('Time'), 'Date'[Time])
var _etime = Maxx(allselected('Time'), 'Date'[Time])
var _st = _sdate + _stime
var _ed = _edate + _etime
var _start = max(Table[take off], _st)
var _end = min(Table[Land on], _ed)
return
calculate(sumx(Table, datediff(_start,_end,second)), filter( Table, (Table[take off] >= _st && Table[take off] <=ed) || (Table[Land on] >= _st && Table[Land on] <=ed) ))

Re: Calculate Dynamically Change Duration

Eesa — Wed, 22 Dec 2021 07:19:02 GMT

Thank you amitchandak for your reply.

I got an error message with max(Table[take off], _st). The message: "A single value for column 'take off' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Re: Calculate Dynamically Change Duration

Eesa — Wed, 22 Dec 2021 18:10:26 GMT

I have re-written the measure to correct the error and works very well in matrix visual but it can't work with card visual as it gives me BLANK.
Here is the measure:

Time in Air =

var sdate = MINX(ALLSELECTED(CalendarDIM), CalendarDIM[Date])

var endate = MAXX(ALLSELECTED(CalendarDIM), CalendarDIM[Date]) + 1

var StartDate = MAX(MAX(Table[take off]), sdate)

var EndDate = MIN(MIN(Table[land on]), endate)

return CALCULATE( sumx(Table, DATEDIFF(StartDate, EndDate,MINUTE)) , FILTER(Table,

(Table[take off] >= StartDate && Table[take off] <= EndDate ) || (Table[land on] >= StartDate && Table[land on] <= EndDate )

Here is the result:

I'll really appreciate if anyone can help me fix this.
Thank you

Re: Calculate Dynamically Change Duration

Anonymous — Fri, 24 Dec 2021 07:21:06 GMT

Hi Eesa ,

Try to create a new measure based on your origianl [Time in Air] measure.

Correct Measure with total = SUMX ( 'Table', [Time in Air] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.