Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table pivoted with 3 columns (and some other calculated ones to test DAXs), and they are 'SENSORDD', 'Attribute' and 'Value', as shown in pic below.
And I created a column named 'PreviousValue' to return the values of previous hour for each category ('Attribute') only when 'SENSORDD' fit into 12:XX:XX AM, or else to return 1. The columns worked pretty well except that it returns blank when 'SENSORDD' fitting into a certain condition, and here's how I wrote the code :
As you can see, all the blank values were returned only when 'SENSORDD' was 12:30:00 AM, and I think the tricky part is what the red-colored part in the code, it works completely fine if I erase that part or sub in the parameter TIME(0,0,0), but when I put that back in with some other parameters (e.g. TIME(2,0,0)) ,it starts to not work with some certain 'SENSORDD' value again.
But Min_date as a calculated column, not as local var, works completely fine.
Much thanks in advance.
Hi @_lasfera
Please try
PreviousValue =
VAR CurrentTime = PRESSURE_NETWORK[SENSORDD]
VAR AttributeTable =
CALCULATETABLE (
PRESSURE_NETWORK,
ALLEXCEPT ( PRESSURE_NETWORK, PRESSURE_NETWORK[Attribute] )
)
VAR PreviousTable =
FILTER ( AttributeTable, PRESSURE_NETWORK[SENSORDD] < CurrentTime )
VAR PreviousTime =
MAXX ( PreviousTable, PRESSURE_NETWORK[SENSORDD] )
VAR PreviousValue =
MAXX (
FILTER ( PreviousTable, PRESSURE_NETWORK[SENSORDD] = PreviousTime ),
PRESSURE_NETWORK[Value]
)
RETURN
IF ( HOUR ( CurrentTime ) = 0, PreviousValue, 1 )
Thanks for your reply! But I can have a look at how you would go about it if only the value of an hour ago, rather than 15 minutes ago, is needed. Had many goes at moditication to your code to fit the need but that didn't work out.
@_lasfera
You mean like this?
PreviousValue =
VAR CurrentTime = PRESSURE_NETWORK[SENSORDD]
VAR AttributeTable =
CALCULATETABLE (
PRESSURE_NETWORK,
ALLEXCEPT ( PRESSURE_NETWORK, PRESSURE_NETWORK[Attribute] )
)
VAR PreviousTime =
CurrentTime - TIME ( 1, 0, 0 )
VAR PreviousValue =
MAXX (
FILTER ( AttributeTable, PRESSURE_NETWORK[SENSORDD] = PreviousTime ),
PRESSURE_NETWORK[Value]
)
RETURN
IF ( HOUR ( CurrentTime ) = 0, PreviousValue, 1 )
Yep, but this goes back to where it all started, blank values were returned when 'SENSORDD' was 12:30:00 AM. Appreciate your effort though.🙏