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

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! 

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    i do not know, it works fine, maybe you just didnt rename datetime to seentime in my statement?

    here is my sample pbix-file with your data example https://ufile.io/wgzpm4jz 

     

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