0

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.

Adam Andersson
  • 113
  • 11
  • what is the condition for your current firmware? also you cannot use measure in filter you will need to use calculated table for it. – AnkUser Feb 24 '20 at 09:19

1 Answers1

1

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/

Olly
  • 7,749
  • 1
  • 19
  • 38
  • Thanks for the answer! Sorry for the late response. While this works for a small amount of data this does not seem to be fit for my use. I keep running out of memory when trying to complete this operation. – Adam Andersson Mar 02 '20 at 13:14
  • 1
    It may be better to add this column in Power Query - create a partition per machine, sort each partition by Time, add a Firmware column ( `if [message_type] = 1 then [message_info] else null` ) and Fill Down, then combine partitions. – Olly Mar 17 '20 at 13:11
  • Thanks for the tip, i solved my problem here. https://stackoverflow.com/questions/60633431/sql-column-containing-current-firmware-string-based-on-occational-messages-wit – Adam Andersson Mar 17 '20 at 13:54