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
PekkaKytosaho
Frequent Visitor

Latest value used in time log data

Pls help,

In my log there are time stamps of events with serial numbers. When event happens I need to fetch what was latest value in 'Set Parameter'-column in this this same particular serial number.

image.png
Picture: example of one Serial Number 9227.

When event happens I need DAX to find parameter in use (the latest 'Set Parameter' before event) value in this Serial Number.

The final goal is to get count of events by Set Parameter.  Please find Sample data in Excel-sheet.

Any help is very much appreciated.

1 ACCEPTED SOLUTION

this will work

= 
VAR CurrentTimeStamp = Table1[Time Stamp]
VAR CurrentSerialNumber = Table1[Serial Number]
VAR ValidTimeStamp =
    IF (
        Table1[Description] = "event",
        CALCULATE (
            MAX ( Table1[Time Stamp] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Serial Number] = CurrentSerialNumber
                    && Table1[Time Stamp] < CurrentTimeStamp
                    && Table1[Description] <> "event"                    
            )
        ),
        CurrentTimeStamp
    )
RETURN
    CALCULATE (
        MAX ( Table1[Set Parameter] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Serial Number] = CurrentSerialNumber
                && Table1[Time Stamp] = ValidTimeStamp
        )
    )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

6 REPLIES 6
Stachu
Community Champion
Community Champion

does it have to be DAX or can you use PowerQuery as well?
if yes then you could use Fill Down functionality in PowerQuery - it may be that I'm simplifying too much, please let me know if it works



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

PowerQuery does not work in this case - there are several serial numbers in the time log, so can't fill down as th latest parameter for the same serial number is usually established many rows ago.  Time log sampleTime log sample

I guess what I am after is how to DAX calculated column of event rows with latest parameter value for the same serial number. 

can you try this

 

=
VAR CurrentTimeStamp = Table1[Time Stamp]
VAR CurrentSerialNumber = Table1[Serial Number]
VAR ValidTimeStamp =
    IF (
        Table1[Description] = "event",
        CALCULATE (
            MAX ( Table1[Time Stamp] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Serial Number] = CurrentSerialNumber
                    && Table1[Time Stamp] < CurrentTimeStamp
            )
        ),
        CurrentTimeStamp
    )
RETURN
    CALCULATE (
        MAX ( Table1[Set Parameter] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Serial Number] = CurrentSerialNumber
                && Table1[Time Stamp] = ValidTimeStamp
        )
    )

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thank you - this helps a lot.
Found one problem still. It returns blank when there are two sequential events.

this will work

= 
VAR CurrentTimeStamp = Table1[Time Stamp]
VAR CurrentSerialNumber = Table1[Serial Number]
VAR ValidTimeStamp =
    IF (
        Table1[Description] = "event",
        CALCULATE (
            MAX ( Table1[Time Stamp] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Serial Number] = CurrentSerialNumber
                    && Table1[Time Stamp] < CurrentTimeStamp
                    && Table1[Description] <> "event"                    
            )
        ),
        CurrentTimeStamp
    )
RETURN
    CALCULATE (
        MAX ( Table1[Set Parameter] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Serial Number] = CurrentSerialNumber
                && Table1[Time Stamp] = ValidTimeStamp
        )
    )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Works well. Thank you very much. 

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.