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

PSRai

Helper III

6 years ago

Hi,

I have applied your syntax, the column is applying the MAX reading to all the column however I only need to apply the MAX reading when the new reading is below the MAX reading, below is what I'm looking for,

Date Time Socket Socket ID Energy Output

18/10/2019 13:01:01 1 0.10 0.10

19/10/2019 13:01:05 1 0.30 0.30

20/10/2019 13:01:07 1 0.50 0.50

21/10/2019 13:01:09 1 0.60 0.60

22/10/2019 13:01:11 1 1.00 1.00

23/10/2019 13:01:15 1 0.00 1.00

24/10/2019 13:01:18 1 0.30 1.30

25/10/2019 13:01:20 1 0.50 1.50

26/10/2019 13:01:22 1 0.70 1.70

27/10/2019 13:01:25 1 0.00 1.00

28/10/2019 13:01:26 1 0.20 1.20

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 ).

Forum Discussion

help