look up or filter data

[GeekAlfPro]

Hello,

i have a table with several equipments.

i want to get the first and the last value of the day for one equipment

in the following table the green and orange value.

but i'm struggling with the dax formulas.

i tried

M_soc_depart = 
var _heure = minx(ALLSELECTED('table'), 'table'[timestamp])
return
CALCULATE(MAX('table'[value], FILTER('table', 'table'[timestamp] = _heure)))

 but it doesn't work, pbi says that i have several values....

any help would be welcome.

[tackytechtom]

Hi @GeekAlfPro ,

I have a solution here:

And these are the measure in DAX:

TomsMaxMeasure = 
CALCULATE ( 
    MAX ( Table5[value]  ),
    ALLEXCEPT ( table5, Table5[id_equipment], Table5[timestamp] )
)

TomsMinMeasure = 
CALCULATE ( 
    MIN ( Table5[value]  ),
    ALLEXCEPT ( table5, Table5[id_equipment], Table5[timestamp] )
)

Does this solve your issue? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[GeekAlfPro]

Hello @tackytechtom ,

thanks for your response.

unfortunately, et because i wasn't explicite enough, it not exactly the solution.

i have outliers, that's why i want to get the first data of the day and the last one

your formulas give me the min and max of the values.

i want the values associated with the min / max of timestamp.

i hope i'm clear enough

[GeekAlfPro · Accepted solution]

I finally manage to get what i want

by using firstnonblankvalue and lastnonblankvalue.

thanks !