Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/rows
Date/Time Socket ID Energy New Column
18/10/2019 - 13:01:01 1 0.1 0.1
18/10/2019 - 13:01:05 1 0.3 0.3
18/10/2019 - 13:01:07 1 0.5 0.5
18/10/2019 - 13:01:09 1 0.6 0.6
18/10/2019 - 13:01:11 1 1.0 1.0
18/10/2019 - 13:01:15 1 0.0 1.0
18/10/2019 - 13:01:18 1 0.3 1.3
18/10/2019 - 13:01:20 1 0.5 1.5
18/10/2019 - 13:01:22 1 0.7 1.7
As you can see the Energy column reading increases at each reading from 18/10/2019 - 13:01:01 however when the socket is switched off the reading then sets to 0.0. what I need to do is bridge the gap. I need to create a new column as above and say *IF the current reading is less then the MAX reading then ADD MAX reading to current reading*. So in this case 1.0 is the MAX reading and lets say 0.0 is current I need to add 1.0 to 0.0.
also their are multiple sockets so it would look more like this,
Date/Time Socket ID Energy New Column
18/10/2019 - 13:01:01 1 0.1 0.1
18/10/2019 - 13:01:01 2 4.3 4.3
18/10/2019 - 13:01:01 3 5.5 5.5
18/10/2019 - 13:01:01 4 1.6 1.6
18/10/2019 - 13:01:01 5 1.2 1.2
18/10/2019 - 13:01:15 1 0.0 1.0
18/10/2019 - 13:01:15 2 0.1 4.4
18/10/2019 - 13:01:15 3 0.2 5.7
18/10/2019 - 13:01:15 4 0.0 1.6
18/10/2019 - 13:01:15 5 0.3 1.5
Hope this makes sense?
Thanks for your reply
when i enter the following code in new column, (changed "Test" to "readings) the "Energy"
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
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:
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?.
Hi @PSRai ,
You could add an index column in the query editor and replace 'Table'[Date Time Socket] with 'Table'[Index]( index column ).
Can anyone urgently help with my query please?
@PSRai
If I understood it clearly please try below code
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |