Forum Discussion

PSRai's avatar
PSRai
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
PSRai's avatar
PSRai
Helper III
6 years ago

Thanks for your reply 

 

when i enter the following code in new column, (changed "Test" to "readings) the "Energy" 

 

Column =
VAR MaxEnergy = CALCULATE(MAX(readings[Energy]),ALLEXCEPT(readings,readings[socket_id]))
RETURN IF(readings[Energy]<MaxEnergy,readings[Energy]+MaxEnergy,readings[Energy])
 
the Energy in the RETURN IF column highlight RED (error)
 
"A single value for column 'Energy' in table 'readings' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"
PSRai's avatar
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's avatar
    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's avatar
      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's avatar
        PSRai
        Helper III
        6 years ago

        Can anyone urgently help with my query please?