Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RalphO
Helper I
Helper I

Total time per day in which a condition is met.

Hey,

 

I'm trying to create a measure that calculates the total time in hours for which several conditions are met. The conditions for example are that the vehicle is not moving, that the engine is on and that the parking brake is pulled (to calculate stationary hours). This has to be calculated for each day seperately. The time is defined in a datetime (yyyy-MM-dd HH-mm-ss) format.

 

Does anyone know how to do this using DAX? 

 

If extra informations is needed please let me know!

 

Ralph

8 REPLIES 8
richbenmintz
Solution Sage
Solution Sage

Hi @RalphO,

 

Sounds like you need a Formula like:

 

formulaName = 

variableName = Calculate(Sum(ofsomething), filter, filter, filter)

return 

sumx(datetable, variableName)

 

Calculate function provides your sumif functionality

SUMX function will calcuate for each row in the Date Table

 

If you can provide a sample .pbix file it would be easier to provide a more exact formula.

 

Hope that helps



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


hi @richbenmintz and @Stachu, thank you for the responses.

 

I'm not sure how to incorporate a pbix file in a post (or is it just sharing a download link?)

 

Anyhow, below is a sample of how the data looks, note that the conditions (velocity of 0 and handbrake of 1) are met twice, which should be added to each other. 

 

Example data.PNG 

Hi @RalphO,

What is your desired result?

If you want to calculate the running total of Handbrake, you could refer to below formula:

Sample data:

1.PNG

Createa a calculated column:

A = CALCULATE(SUM(Table1[Handbrake]),FILTER('Table1','Table1'[Time]<=EARLIER('Table1'[Time])))

1.PNG

If you just want to calculate the running total of Handbrake=1, you could refer to below formula:

B = var a=CALCULATE(SUM(Table1[Handbrake]),FILTER('Table1','Table1'[Time]<=EARLIER('Table1'[Time])))
    return IF([Handbrake]=0,0,a)

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@RalphO

 

Can you provide an excel or csv doc with the data, you can attach the file through a link to shared cloud storage, there should also be the ability to attach a file to the thread 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@richbenmintz https://wetransfer.com/downloads/de522b11b144295d90857c09a0895a9520181106084303/d5c291dc07213f6f9573... for the data.

 

So what I would like to have is the total time in which both velocity is 0 and the handbrake is 1. It has to be done with a measure since I'm using streamed data so I can not create a calculated column.

 

 

Hi @RalphO,

 

Not sure why I can't attach a file but please find a link to a pbix file that i believe solves your issue.

link to pbix

 

Steps required:

1. In Power Query -> create an index column -> determine prior row time value based on Index -> subtract current time from prior time

2. Create Measure to capture time when stopped -> 

Time Stopped = CALCULATE(sum(Blad1[Time Betwen Previous Reading]), FILTER('Blad1', Blad1[Velocity] = 0 || Blad1[Handbrake] = 1))

 

I would suggest that finding the prior row time value would be better done at the source of the data and not with Power Query as I am not sure how well Power Query will handle this windowed workload over a large data set

 

Pic of Results.PNG



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @RalphO,

You could refer to below measure:

Measure = CALCULATE(SUM('Table1'[Handbrake]),FILTER(ALL(Table1),'Table1'[Time]<=MAX('Table1'[Time])))

Result:

1.PNG

 Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stachu
Community Champion
Community Champion

can you share sample anonymised data?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.