Forum Discussion

sqaar__'s avatar
sqaar__
Icon for Helper I rankHelper I
3 years ago

Duration from one date filterd by category and value

Hello Community! 


I have signal data from the machine and need to do a spectrum of work time for each machine.

The table contains, the name of the machine, status 0/1 ( downtime/working), and Data with time.

What i need is time how long was value 0 before it change to 1 (its downtime) and also the duration from 1 to 0 (work time)


I tried to implement the Lag & Lead solution but it fails, also I did it in power query duration, but it was very slow - the table contains millions of rows.

Could you recommend a DAX solution for this one which will work very fast?

 

Part of the table below:

 

Thank you so much, all hints are appreciated. 

 

Best,

Patryk

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Downtime = CALCULATE( SUMX(FILTER(ALL(Table), Table[Status] = 0), Table[Duration]), FILTER(ALL(Table), Table[Status] = 0) )
    Try this 🙂 

    • sqaar__'s avatar
      sqaar__
      Icon for Helper I rankHelper I
      3 years ago

      Hi Anonymous 

       

      Thank you so much for your answer:) 
      Unfortunately, I don't have a duration column here. It's the main issue here, to count duration correctly. 
      It can't be the previous row, after sorting by time, cause still need to filter other machines.

      Any other ideas?