Forum Discussion

Helper III

6 years ago

help

currently have data where by its measuring usage of each electrical sockets, Energy by date time (seconds). I need to create a new column as below. Cant be done in excel due to the amount of data/ro...

Need Help

v-eachen-msft

Community Support

6 years ago

Hi PSRai

You could refer to the following DAX:

Output =
VAR maxenerge =
    CALCULATE ( MAX ( 'Table'[Energy] ), ALLEXCEPT ( 'Table', 'Table'[Socket ID] ) )
RETURN
    IF (
        'Table'[Date Time Socket]
            <= CALCULATE (
                LOOKUPVALUE ( 'Table'[Date Time Socket], 'Table'[Energy], maxenerge ),
                ALLEXCEPT ( 'Table', 'Table'[Socket ID] )
            ),
        'Table'[Energy],
        maxenerge + 'Table'[Energy]
    )

Here is the result:

PSRai

Helper III

6 years ago

Thank you

Im getting the following error message

"A table of multiple values was supplied where a single value was expected"

can you advise please?.

PSRai
Helper III
6 years ago
Can anyone urgently help with my query please?
v-eachen-msft
Community Support
6 years ago
Hi PSRai ,

You could add an index column in the query editor and replace 'Table'[Date Time Socket] with 'Table'[Index]( index column ).