Latest status based on date(time) and measurement point

[Anonymous]

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!

[tamerj1]

Hi @Anonymous 

please try

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

[Anonymous]

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:

[tamerj1]

@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"
            )
        )
    )
)

[Anonymous]

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
)

[tamerj1 · Accepted solution]

@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"
            )
        )
    )
)

[Anonymous]

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.

[Anonymous]

Thank you so much, this has been working solution!