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.
Good morning,
I wish to return a time (duration) in seconds from a certain element.
As soon as I get the label "Ordre fermeture vanne de garde" which is a closing order for a valve, i want to get the duration till i reach "Vanne de garde fermée" which indicates, thanks to a sensor, that the valve is closed. And i have to pay attention to which valve orders is, for this i got a column name "EGF".
This is how some dummies data looks like. I start a duration from a "Présente" (present) statement closing order, and finish to the first "Présente" statement closed valve. As i said before, i only return a time from same EGF.
Here you can download some datas : download link
(password is : datediff)
I've tried some method, the more close one is this one :
Durée(secondes) = IF('test_vdg'[Description de l'évenement] = "Vanne de garde fermée";
DATEDIFF(
CALCULATE(
MAX('test_vdg'[Date et heure]);
FILTER(
ALLEXCEPT(
'test_vdg';
'test_vdg'[EGF]);
'test_vdg'[Description de l'évenement] < EARLIER('test_vdg'[Description de l'évenement])));
'test_vdg'[Description de l'évenement]; SECOND); 0)
But it only return the duration from the previous label and it's not what i'm looking for.
If you need few more explainations, i'm here.
Solved! Go to Solution.
I figured it out,
thank you @v-xicai for the referral by using variables.
For those of you who might be interested, here's the final formula :
Durée fermeture(secondes) =
Var _DebutDate =
CALCULATE(
MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
FILTER(
ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]),
GU_BasseMaulde_CDEVDG[Date et heure]
< EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Ordre fermeture vanne de garde" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))
Var _FinDate =
CALCULATE(
MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
FILTER(
ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]),
GU_BasseMaulde_CDEVDG[Date et heure]
<= EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))
RETURN
IF('GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && 'GU_BasseMaulde_CDEVDG'[Etat de l'évenement] = "Présente", DATEDIFF(_DebutDate , _FinDate, SECOND), 0)
Hi @Anonymous ,
You may create measure like DAX below.
Durée(secondes) =
Var _FirstDate= CALCULATE(MIN('test_vdg'[Date et heure]),FILTER(ALLSELECTED('test_vdg'), 'test_vdg'[EGF] =MAX('test_vdg'[EGF])&& 'test_vdg'[Date et heure]< MAX('test_vdg'[Date et heure])))
Var _LastDate= CALCULATE(MAX('test_vdg'[Date et heure]),FILTER(ALLSELECTED('test_vdg'), 'test_vdg'[EGF] =MAX('test_vdg'[EGF])&& 'test_vdg'[Date et heure]<= MAX('test_vdg'[Date et heure])&&'test_vdg'[Description de l'évenement] = "Vanne de garde fermée"))
Return
IF('test_vdg'[Description de l'évenement] = "Vanne de garde fermée", DATEDIFF( _FirstDate ,_LastDate, SECOND), 0)
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
the dax formula only return one result for every "Vanne de garde fermée" which is 31915350.
I figured it out,
thank you @v-xicai for the referral by using variables.
For those of you who might be interested, here's the final formula :
Durée fermeture(secondes) =
Var _DebutDate =
CALCULATE(
MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
FILTER(
ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]),
GU_BasseMaulde_CDEVDG[Date et heure]
< EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Ordre fermeture vanne de garde" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))
Var _FinDate =
CALCULATE(
MAX('GU_BasseMaulde_CDEVDG'[Date et heure]),
FILTER(
ALLEXCEPT('GU_BasseMaulde_CDEVDG', GU_BasseMaulde_CDEVDG[EGF], GU_BasseMaulde_CDEVDG[Site]),
GU_BasseMaulde_CDEVDG[Date et heure]
<= EARLIER('GU_BasseMaulde_CDEVDG'[Date et heure]) && 'GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && GU_BasseMaulde_CDEVDG[Etat de l'évenement] = "Présente"))
RETURN
IF('GU_BasseMaulde_CDEVDG'[Description de l'évenement] = "Vanne de garde fermée" && 'GU_BasseMaulde_CDEVDG'[Etat de l'évenement] = "Présente", DATEDIFF(_DebutDate , _FinDate, SECOND), 0)
Am i correct in thinking that for "G01" you're looking for the difference in time for these two rows:
Which should be something along the line of 2 hours and 34 min ?
/J
No your not,
like i put on the 2nd picture, i need to get the time between the "start point" to the "finish point"
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |