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

     

    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      try to create new measure, not column

       

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

      • Anonymous's avatar
        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!