Forum Discussion
Calculated measure with filter
Hi there!
I would like to answear the qestion, "how many kilometers have been driven since the last inspection?" Was the inspection (KontrOelAdblueetc) made then it's TRUE. So I somehow need to get to the last TRUE and sum up the distance (Gefahrene Distanz) or subtract the last KM of FALSE to the KM of TRUE. How to I get all this in a measure?
Maybe Anonymous you could help me out with that one as well... ? 🙂
Hi MarcDT21
I didn't know what FZ column represents for so I ignored that in previous reply. Anyway, try this new measure 2:
Measure 2 = VAR current_FZ_table = FILTER(ALL('Table'),'Table'[FZ]=SELECTEDVALUE('Table'[FZ])) VAR last_true_date = MAXX(FILTER(current_FZ_table,'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) VAR last_true_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_true_date),'Table'[KMEnde]) VAR last_date = MAXX(current_FZ_table,'Table'[Datum]) VAR last_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_date),[KMEnde]) RETURN last_date_KM - last_true_date_KMRegards,
Jing
If this post helps, please Accept it as Solution to help other members find it.
9 Replies
last_true_date=VAR _current=max(date[date]) RETURN maxx(filter(all(date[date]),date[date]<_current&&[KontrOelAdblueetc]),date[date])
dear wdx223_Daniel I thought I used your querry as intended, but results tell me otherwise. It gives me an odd date back. Could you please help me showing what I did wrong?
dear @wdx223_Daniel I thought I used your querry as intended, but results tell me otherwise. It gives me an odd date back. Could you please help me showing what I did wrong?
- last_true_date = VAR _current=MAX(CO2DatenApp[Datum]) RETURN MAXX(FILTER(ALL(CO2DatenApp), CO2DatenApp[Datum] <_current&&[KontrOelAdblueect]),CO2DatenApp[Datum])
Hi MarcDT21
You can try these measures
Measure = VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) RETURN SUMX(FILTER(ALL('Table'),'Table'[Datum]>last_true_date),'Table'[GefahreneDistanz])or
Measure 2 = VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) VAR last_true_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=last_true_date),'Table'[KMEnde]) VAR last_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=MAX('Table'[Datum])),[KMEnde]) RETURN last_date_KM - last_true_date_KMLet me know if you have any questions.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.Dear v-jingzhang
Thank you very much for you two measures. The second measure is exactly what I was looking for. Unfortunately I still have troubles.
When I try out the measure with steps then (e.g. first give me back the "last_true_date" then it will give me for all cars the last date of the last true - instead of each and every car its own last true date. -> see first picture.
Thank you very much for helping me!
Results with measure2
results with measure 2Measure with steps
last true datelast km when true date
Hi MarcDT21
I didn't know what FZ column represents for so I ignored that in previous reply. Anyway, try this new measure 2:
Measure 2 = VAR current_FZ_table = FILTER(ALL('Table'),'Table'[FZ]=SELECTEDVALUE('Table'[FZ])) VAR last_true_date = MAXX(FILTER(current_FZ_table,'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) VAR last_true_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_true_date),'Table'[KMEnde]) VAR last_date = MAXX(current_FZ_table,'Table'[Datum]) VAR last_date_KM = MAXX(FILTER(current_FZ_table,'Table'[Datum]=last_date),[KMEnde]) RETURN last_date_KM - last_true_date_KMRegards,
Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @MarcDT21
You can try these measures
Measure = VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) RETURN SUMX(FILTER(ALL('Table'),'Table'[Datum]>last_true_date),'Table'[GefahreneDistanz])or
Measure 2 = VAR last_true_date = MAXX(FILTER(ALL('Table'),'Table'[KontrOelAdblueect]=TRUE()),'Table'[Datum]) VAR last_true_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=last_true_date),'Table'[KMEnde]) VAR last_date_KM = MAXX(FILTER(ALL('Table'),'Table'[Datum]=MAX('Table'[Datum])),[KMEnde]) RETURN last_date_KM - last_true_date_KMLet me know if you have any questions.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.v-jingzhang thank you very very much!!!! this measure is going to be one of the most important KPI in the safety of our company fleet/drivers!
