Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi,
I need to display for every person how long she worked each day and I need to display the summarized hours of all persons. It should be displayed in the format hh:mm.
Therefore I use a measure to convert the decimal time [IST] to minutes and another measure to convert from minutes to hh:mm.
minutes = TRUNC([IST])*60+(([IST])-Trunc([IST]))*60
hh:mm = [minutes]/60/24
It looks like this:
I don't really need to display the display the colums Ist and minutes.
I only need to display the column hh:mm and the total sum below.
How could I achieve that?
Many thanks for hints.
Solved! Go to Solution.
Then display results in visuals as text (eg display 65.5 mins = 01:05:30)
I have taken trouble to help you, now please help me by giving kudos.
Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.
Thanks.
Total duration in MM:SS =
-- convert decimal duration to hh:mm:ss text
-- eg converts 65.5 to "01:05:30" and converts 0 to 00:00
-- Take decimal minutes as input eg 5.5 minutes
VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])
-- Convert decimal minutes to seconds
VAR Duration = DecimalMinutes * 60
-- Calc the days, hours, minutes and seconds (add days if required
VAR Hours = INT(DIVIDE(Duration,3600,0))
VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
-- Format hours, minutes and seconds to two decimals
VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))
RETURN
-- if input is 0 return 00:00.
-- return hours:mins:seconds as text
-- handles any errors(unlikely)
-- if input is greater than 24hrs then the hours will be displayed
IFERROR(
IF (DecimalMinutes>0,
H & ":" & M & ":" & S,
"0:00"),
"Error")
Then display results in visuals as text (eg display 65.5 mins = 01:05:30)
I have taken trouble to help you, now please help me by giving kudos.
Please click the thumbs up if you like me trying to help you and click Accept as Solution if I have fixed you problem.
Thanks.
Total duration in MM:SS =
-- convert decimal duration to hh:mm:ss text
-- eg converts 65.5 to "01:05:30" and converts 0 to 00:00
-- Take decimal minutes as input eg 5.5 minutes
VAR DecimalMinutes = AVERAGE(Calls[Conversation duration])
-- Convert decimal minutes to seconds
VAR Duration = DecimalMinutes * 60
-- Calc the days, hours, minutes and seconds (add days if required
VAR Hours = INT(DIVIDE(Duration,3600,0))
VAR Minutes = INT(DIVIDE(MOD( Duration - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)
-- Format hours, minutes and seconds to two decimals
VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))
RETURN
-- if input is 0 return 00:00.
-- return hours:mins:seconds as text
-- handles any errors(unlikely)
-- if input is greater than 24hrs then the hours will be displayed
IFERROR(
IF (DecimalMinutes>0,
H & ":" & M & ":" & S,
"0:00"),
"Error")
You could just do
hh:mm = TIME(0, [IST]*60, 0)
--Nate
For me it works nice. Thanks:)
Thank you, it works partially. I see the values as expected. How do I get the values summed up?
I could select Do not Summarize, Count or Count distinct from Default Summarization. There are no options to select any further items like Sum.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
User | Count |
---|---|
23 | |
10 | |
9 | |
8 | |
8 |