Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
Solved! Go to 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 ) )
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
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.
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 ) )
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 ) )
Works well. Thank you very much.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |