topic Re: Latest status based on date(time) and measurement point in DAX Commands and Tips

Latest status based on date(time) and measurement point

Anonymous — Wed, 31 May 2023 05:51:05 GMT

I have a problem which I can't resolve. Case is that we have machines with several measurement points per machine and three different statuses (=warning types). I have COUNTROWS measure for each warning type and those are in table visual where every machine has it's own row.

Problem is how can I get latest warning type for each measurement point based on latest measured datetime? It would be easy with just LASTDATE function but when measurement dates differ between measurement point this solution won't work. I need to group measurement point and date or datetime with somehow. I tried just GROUPBY( alarms_table, measurement_point, date ) in CALCULATE function but that won't work.

Here is quick sample data that I just made with excel. I highlighted latest measurement date for point P02 which is the problem in this example.

Can someone help? Thanks!

Re: Latest status based on date(time) and measurement point

tamerj1 — Wed, 31 May 2023 07:05:05 GMT

Hi Anonymous

please try

Latest Warning = MAXX ( TOPN ( 1, FILTER ( 'Table', 'Table'[Warning Type] <> "Ok" ), 'Table'[DateTime] ), 'Table'[Warning Type] )

Re: Latest status based on date(time) and measurement point

Anonymous — Wed, 31 May 2023 07:58:00 GMT

Thank you, I will try that. But I am counting all warning types because there can be several alarms active from several different measurement source system. So basically

Source1 - P02
Source1 - P03
Source1 - P04
Source2 - P01
Source2 - P02
Source2 - P03

I need to count latest warning types from every different source. Here is new picture:

Re: Latest status based on date(time) and measurement point

tamerj1 — Wed, 31 May 2023 08:29:08 GMT

Anonymous
I think I misunderstood the requirement. I believe you want to create three measures (one for each status) to count based on the latest status of each warning type. You can use the following measure structure for "Danger" and "Warning" as well.

Ok = SUMX ( VALUES ( 'Table'[Measurement_Point] ), INT ( NOT ISEMPTY ( FILTER ( TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ), 'Table'[Warning_Type] = "Ok" ) ) ) )

Re: Latest status based on date(time) and measurement point

Anonymous — Wed, 31 May 2023 12:50:00 GMT

Thank you so much, this seems to work perfectly! When I get more data I can validate this better. One thing though. This works in table rows but in totals it won't count all latest warning types from all machines. So basically it need some tweaking when counting totals like this

Ok = VAR _latestAlarm = SUMX ( VALUES ( 'Table'[Measurement_Point] ), INT ( NOT ISEMPTY ( FILTER ( TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ), 'Table'[Warning_Type] = "Ok" ) ) ) ) VAR _countTotal = previous code with some changes RETURN IF( HASONEVALUE( machine_id ), _latestAlarm, _countTotal )

Re: Latest status based on date(time) and measurement point

tamerj1 — Wed, 31 May 2023 13:20:26 GMT

Anonymous
Please try

Ok = SUMX ( SUMMARIZE ( 'Table', 'Table'[Machine_ID], 'Table'[Measurement_Point] ), INT ( NOT ISEMPTY ( FILTER ( TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ), 'Table'[Warning_Type] = "Ok" ) ) ) )

Re: Latest status based on date(time) and measurement point

Anonymous — Tue, 06 Jun 2023 11:36:56 GMT

Thank you so much, this has been working solution!

Re: Latest status based on date(time) and measurement point

Anonymous — Tue, 06 Jun 2023 14:40:21 GMT

Actually I just figured out at there can be ties in DateTime... So there can be "OK" and "Warning" with same timestamp. Is there easy solution to rank those? Luckily I have one other ID column which I can use to break those ties but I don't know how.

Edit. I think I got it 🙈. Just added after [DateTime]:
" , , 'Table'[Alarm_id] " and it seems to give correct results.