Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone!
I'm having an issue trying to calculate the amount of time that a certain condition is valid in a set of data. See graph below. I'm trying to calculate what amount of time the Feed Rate in this case is above 800 tph. The formula I currently have is as follows:
Time [>800 tph] = CALCULATE( ---what function can I use here?--- , 'Feed Rate'[Value]>800 )
What DAX function can I use to calculate time duration? (For which the data is above 800 tph)
Solved! Go to Solution.
@Rajesh_G,
Please check DAX in the attached PBIX file.
Regards,
Lydia
@Rajesh_G,
Please use the DAX below.
Time>800 tph = CALCULATE( COUNTA('Feed Rate'[Time]) , 'Feed Rate'[Value]>800 )
Regards,
Lydia
Hi Lydia
Thank you for your response. However, that seems to calculate the AMOUNT of values rather than the actual TIME that the values are above 800.
Kind regards
Rajesh
@Rajesh_G,
What is your expected result based on the sample data I post?
Regards,
Lydia
It would be "2 hours" (from 7AM to 9AM) assuming that the hypothetical 10 AM would have a value below 800
@Rajesh_G,
Create the following column.
Column = IF('Feed Rate'[Value]>800,1,0)
Then create the following measures. If the Dax don't return your expected result, please share sample data of your table here.
MinTime = CALCULATE(MIN('Feed Rate'[Time]),FILTER('Feed Rate','Feed Rate'[Column]=1))
MaxTime = CALCULATE(MAX('Feed Rate'[Time]),FILTER('Feed Rate','Feed Rate'[Column]=1))
TimeAmount = LEFT(([MaxTime]-[MinTime]),1)
Regards,
Lydia
Hi Lydia
Thank you for your response.
1. Why do you use the "LEFT" function if I may ask?
2. The method proposed does give the desired result but only if theres one continuous occurence of the condition. For instance, if in your dataset you could add that at 11 AM the value is 850 and at 12:00 its 900 and finally at 13:00 its 300 again, would this formula pick up that the total is 3 hours (2 hours between 7 and 9 and 1 hour between 11 and 12)?
Kind regards
Rajesh
@Rajesh_G,
The Left function is to get duration hour, otherwise the measure will show in Time formt. Please share sample data of your table and post expected result based on sample data .
Regards,
Lydia
Hi Lydia
Please see below an example set of data. Notice how the TimeAmount measure calculates the total time at which the condition is valid to be 6 hours whereas it is actually supposed to be 3 hours.
Kind regards
Rajesh Gowda
@Rajesh_G,
Please check DAX in the attached PBIX file.
Regards,
Lydia
Thank you Lydia for the great solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
88 | |
66 |