Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
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:
Createa a calculated column:
A = CALCULATE(SUM(Table1[Handbrake]),FILTER('Table1','Table1'[Time]<=EARLIER('Table1'[Time])))
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)
You could also download the pbix file to have a view.
Regards,
Daniel He
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
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.
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
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:
Regards,
Daniel He
can you share sample anonymised data?
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |