Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    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

    LinkedIn

az38's avatar
az38
Community Champion
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

LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi az38 , thanks for your reply but I am getting 1 min for each row instead of getting the dwell time. 😞 Is there any other possible way? Thanks for your help!

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Hi Anonymous 

      for me it works fine with your data sample

      maybe you have something else in your data model? or your datetime field has no datetime data type?

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi az38, thanks for your reply. My DateTime is definitely in DateTime format, but I just found out that this line didn't catch the date from DateTime, is there anything that went wrong?

        var _selectedDay = DATE(YEAR(SELECTEDVALUE('Table'[DateTime])), MONTH(SELECTEDVALUE('Table'[DateTime])),DAY(SELECTEDVALUE('Table'[DateTime])))

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks az38 ! I guess this will definitely works on a smaller dataset. My dataset is a bit too huge and I have split it up to three parts and it is working fine now!