Finding dwell time

Hi, I am struggling to find dwell time with dax, would really appreciate if anyone could guide me on this. Sample dataset: DateTime Type 1/1/2020 12:00 abc 1/1/2020 12:30 abc 1/...

Need Help

az38

6 years ago

Hi Anonymous

try to create a measure

dwellTime = 
var _selectedDay = DATE(YEAR(SELECTEDVALUE('Table'[DateTime])), MONTH(SELECTEDVALUE('Table'[DateTime])),DAY(SELECTEDVALUE('Table'[DateTime])))
var _start = CALCULATE(MIN('Table'[DateTime]),FILTER(ALL('Table'),'Table'[Type]=SELECTEDVALUE('Table'[Type]) && DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))=_selectedDay))
var _end = CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[Type]=SELECTEDVALUE('Table'[Type]) && DATE(YEAR('Table'[DateTime]),MONTH('Table'[DateTime]),DAY('Table'[DateTime]))=_selectedDay))
return
IF(_end>_start,datediff(_start,_end,MINUTE),1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

Anonymous

Not applicable

6 years ago

This is what I got from my data, the left table is the original data and the right table is the _selectedDay measure and dwellTime measure, and I really have no idea why this is not working. Please guide me on this, thanks a bunch!