CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Adam Andersson
Adam Andersson

Reputation: 113

PowerBI: Calculate column containing current firmware based on occational messages

Table called MachineEvents

I have a table called MachineEvents containing data similar to the one seen above.

I'm trying to create a measured column in DAX containing the current firmware of the machine. This will be used to filter only messages when the machines has had certain firmware.

I've tried searching around but i have trouble finding similar problems.

Upvotes: 0

Views: 31

Answers (1)

Olly
Olly

Reputation: 7891

Assuming you want your Firmware column to return the latest value from field message_info where message_type = 1, based on event_time, then use this DAX code in your Calculated Column:

Firmware = 
VAR LastFirmwareTime = 
    CALCULATE ( 
        MAX ( MachineEvents[event_time] ),
        FILTER ( 
            ALLEXCEPT ( MachineEvents, MachineEvents[machine_id] ),
            MachineEvents[message_type] = 1 && MachineEvents[event_time] <= EARLIER ( MachineEvents[event_time] )
        )
    )
RETURN
    CALCULATE ( 
        VALUES ( MachineEvents[message_info] ),
        FILTER ( 
            ALLEXCEPT ( MachineEvents, MachineEvents[machine_id] ),
            MachineEvents[message_type] = 1 && MachineEvents[event_time] = LastFirmwareTime
        )
    )

See worked example PBIX file here: https://pwrbi.com/so_60372050/

Upvotes: 1

Related Questions