Measure count of the latest value

[jamienourish]

I have the following data i am sending using the api, so I can not create new columns only measures

user_id	State	updated_at
1	OFF	05/30/18 03:27:32 PM
2	ON	05/30/18 03:28:32 PM
1	ON	05/30/18 03:30:32 PM
2	OFF	05/30218 03:34:3PM
3	ON	05/30218 04:34:3PM

I would like to display a visual "CARD" with the count of all ON's, so I need to take the latest state for the User ID

ON = 2

OFF = 1

Please help

[alexei7 · Accepted solution]

Hi,

The following works for me, can you give it a go and see if it's giving you the correct results:

Measure = CALCULATE(COUNTROWS(FILTER(Table1, Table1[updated_at]=CALCULATE(max(Table1[updated_at]),ALLEXCEPT(Table1,Table1[user_id])))),Table1[State]="ON") 

Thanks

Alex 

[jamienourish]

Thank you very much for your reply this is working well, I had tried similar queries but could not get it to work.