Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mgz_00
Frequent Visitor

Latest status based on date(time) and measurement point

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.

latest_warning_type.png

 

Can someone help? Thanks!

1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

Hi @mgz_00 

please try

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

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:

latest_warning_type.png

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

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
)

 

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

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.

Thank you so much, this has been working solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors