Forum Discussion
Solved
Transpose table with calculation
Dear all, I want to transpose the table: Time Phase 01:05 Start heating 05:50 End heating 06:00 Start Cooling 12:17 End cooling Into this form: Duration Phase ...
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
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 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