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: 

DateTimeType
1/1/2020 12:00abc
1/1/2020 12:30abc
1/1/2020 12:31xyz
1/1/2020 13:30xyz
1/1/2020 14:00 abc
2/1/2020 10:20abc
2/1/2020 11:15xyz
2/1/2020 11:16 abc
2/1/2020 13:15efg
2/1/2020 16:00xyz
3/1/2020 13:30abc
3/1/2020 14:00 abc
3/1/2020 15:00 xyz
3/1/2020 15:15xyz

 

This is what I want: 

DateTimeTypeDwell time (min)
1/1/2020 12:00abc120
1/1/2020 12:30abc120
1/1/2020 12:31xyz61
1/1/2020 13:30xyz61
1/1/2020 14:00 abc120
2/1/2020 10:20abc56
2/1/2020 11:15xyz285
2/1/2020 11:16 abc56
2/1/2020 13:15efg1
2/1/2020 16:00xyz285
3/1/2020 13:30abc30
3/1/2020 14:00 abc30
3/1/2020 15:00 xyz15
3/1/2020 15:15xyz15

 

I am trying to find the dwell time for each type on daily basis, is there any possible way to do it? Have you ever seen anything like this? Would really appreciate some help! Thanks xoxo

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

10 Replies

  • 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

      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!