Forum Discussion
Transpose table with calculation
CornelisV This will calculate the number of minutes in a column. See attached PBIX below sig.
Column = VAR __Phase = [Phase] VAR __Current = [Date time] VAR __Type = SWITCH( TRUE(), __Phase = "End heating", "Start heating", __Phase = "End cooling", "Start Cooling" ) VAR __Result = SWITCH( __Phase, "Start heating", 0, "Start cooling", 0, VAR __DateTime = [Date time] VAR __Result = MAXX( FILTER( ALL( 'Table' ), [Date time] < __DateTime && [Phase] = __Type ), [Date time] ) RETURN __Current - __Result ) RETURN __Result * 60 * 24
Dear Anonymous and Greg_Deckler ,
Thank you very much for thinking along!
Looking at your reply, it seems a challenging task to find a proper solution.
First at all: it is correct that more than one cooling and/or heating will occur from the same table.
Likely, the solution has one drawback: it cannot make any difference when heating start at 10th November and end at 11th November.
What about this data, where datetime is included:
| Date | Time | Date time | Phase |
| 10-11-2024 | 01:05 | 10-11-2024 01:05 | Start heating |
| 10-11-2024 | 05:50 | 10-11-2024 05:05 | End heating |
| 11-11-2024 | 06:00 | 11-11-2024 06:00 | Start Cooling |
| 11-11-2024 | 12:17 | 11-11-2024 12:17 | End cooling |
| 11-11-2024 | 13:00 | 11-11-2024 13:00 | Start heating |
| 12-11-2024 | 00:29 | 12-11-2024 00:29 | End heating |
As you can see, date and time will be helpfull, but the column with date cannot be used as anchor, so a new column may be created where ID = 1,1,2,2,3,3.
What do you think?
Best regards,
Cornelis
CornelisV This will calculate the number of minutes in a column. See attached PBIX below sig.
Column =
VAR __Phase = [Phase]
VAR __Current = [Date time]
VAR __Type =
SWITCH( TRUE(),
__Phase = "End heating", "Start heating",
__Phase = "End cooling", "Start Cooling"
)
VAR __Result =
SWITCH(
__Phase,
"Start heating", 0,
"Start cooling", 0,
VAR __DateTime = [Date time]
VAR __Result = MAXX( FILTER( ALL( 'Table' ), [Date time] < __DateTime && [Phase] = __Type ), [Date time] )
RETURN
__Current - __Result
)
RETURN
__Result * 60 * 24Dear Greg_Deckler ,
This is the very quick solution in one word: wow!
Thank you very much.
One question: can you give me a clue where can I find the best documentation, url, etc how to deal with DAX programming as what you have demonstrated above?
best regards,
Cornelis