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

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])))

     

    • az38's avatar
      az38
      Community Champion
      6 years ago
      Anonymous
      Are you trying to create a measure?
      Maybe you should just replace “,” to “;” due to localization question.
      One more option - share your pbix file without sensitive data and give me a link to help you 🙂
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I was trying to create a column for dwell time. 

        The comma is working fine for me, but the semicolon is giving me errors. 

        I am sorry I couldn't share the data due to privacy concerns, it looks exactly like the sample data but with seconds (e.g. 1/12/2020 01:12:25).

        I've tried to create another column for "_selectedDay", but it returned blank, I thought it supposed to return the date?  

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