topic Re: Convert Number into hours and minutes in DAX Commands and Tips

Convert Number into hours and minutes

Anonymous — Sun, 05 Mar 2023 20:29:58 GMT

Hello Power Bi super solvers,

Could you help me get over the hill by converting a number into hours?

Background: My data has a field - 30-minute session. The row has 0, 1, 2, 3, etc. Each number represents a 30-minute session. For example, 4 means four sessions at 30 minutes each. Therefore, it should equal two hours.

Could you nudge me in the right direction to show the output as E.G. 11:30, 24:00, etc.?

Any resource is greatly appreciated.

Thank you

Luis

Sample Tables

Student Name	Student ID	Tutor	Sessions Week	30-minute Sessions	Sessions Weeks (Groups)
Luna Jeremiah	82272	Binder, Judy	2-1 Sessions	11	First Six Weeks
Luna Jeremiah	82272	Binder, Judy	3-1 Sessions	8	Second Six Weeks
Luna Jeremiah	82272	Binder, Judy	3-3 Sessions	2	Third Six Weeks
Monarrez, Kamila	46278	Binder, Judy	1-4	12	First Six Weeks
Monarrez, Kamila	46278	Binder, Judy	2-4	12	Second Six Weeks
Monarrez, Kamila	46278	Binder, Judy	3-4	4	Third Six Weeks

Envisioned Output

Student Name	Student ID	First Six Weeks	Second Six Weeks	Third Six Weeks	Total Hours (HH: MM)
Monarrez, Kamila	46278	06:00	06:00	02:00	14:00
Luna Jeremiah	82272	05:30	04:00	01:00	10:30

Could someone point me in the correct direction?

Thank you.

Re: Convert Number into hours and minutes

Anonymous — Sun, 05 Mar 2023 20:42:37 GMT

forgot to add that i am stuck on this DAX

Tutoring in hours =

Var Total_Hours =

SUMX(

'Tutorial Log',

HOUR('Tutorial Log'[30-minute Sessions] + MINUTE('Tutorial Log'[30-minute Sessions])/60

)

Return

Re: Convert Number into hours and minutes

andhiii079845 — Sun, 05 Mar 2023 20:44:09 GMT

What happend if you have more than 24h (48 sessions x 30 min)?

Column =

var _min = 'Table (2)'[Column1]*30

var hourNo=INT(_min/60)

var minuteNO=MOD(_min,60)

RETURN FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")

You can also change to data type time with the column.

Re: Convert Number into hours and minutes

Anonymous — Sun, 05 Mar 2023 21:01:39 GMT

andhiii079845 - Thank you.

It worked exactly as expected.

Appreciate your willingness to share your expertise.

Re: Convert Number into hours and minutes

andhiii079845 — Mon, 06 Mar 2023 08:54:13 GMT

Can you share please all involved column for your dax expression?

Re: Convert Number into hours and minutes

Anonymous — Wed, 08 Mar 2023 23:09:43 GMT

andhiii079845

Apologies for the delayed response.

Total Tutoring Hours = SUM(Separate[Tutoring Time (30 Minutes each session)])

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

Is that the information you were asking for me to provide?

Re: Convert Number into hours and minutes

andhiii079845 — Thu, 09 Mar 2023 05:35:37 GMT

I see now that you forget something after RETURN. There has to be s calculations or a return of a variable

Re: Convert Number into hours and minutes

Anonymous — Thu, 09 Mar 2023 14:43:11 GMT

Hi andhiii079845 - i think i see what you mean.

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

HourNo, minuteNo

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

Could you give me another friendly nudge in correcting the syntax? I believe I want variables HourNo and MinuteNo to be returned in the DAX.

Thank you for everything.

Re: Convert Number into hours and minutes

andhiii079845 — Thu, 09 Mar 2023 19:30:46 GMT

Sorry for my late answer.

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

HourNo, minuteNo

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

You can not return 2 values in one column. Like: 60 20. You have to transform it with
FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00") in one value per row. Or you create two columns. One for hours, one for minutes.

If you have futher question, let us know what is your goal, your involved columns/table and your desired result. One good example can make everything more clear. 🙂

Re: Convert Number into hours and minutes

Anonymous — Thu, 09 Mar 2023 20:00:02 GMT

Hi andhiii079845,

No worries about the timing of the response. I appreciate your follow-up.

I am still learning the syntax, but your response indicates that this syntax is incorrect - per the error message. I have played around with the syntax but can't get it correct.

This visual almost does what I want.

The tooltip is great

I want the Y axis to show the number of tutoring hours in HH:MM.

Any gentle nudge on my syntax is greatly appreciated. I still can't upload a PBIX. Wish that I could, would make my message and intent clearer.

Re: Convert Number into hours and minutes

andhiii079845 — Thu, 09 Mar 2023 20:03:54 GMT

Try this. It is great that you want to understand the synthax! 🙂

Tutoring in HH:MM =

VAR _Min = [Total Tutoring Hours]*30

VAR HourNo = INT(_Min/60)

VAR minuteNo = MOD(_Min, 60)

Return

FORMAT(HourNo, "#00") & ":" & FORMAT(minuteNo, "#00")

Re: Convert Number into hours and minutes

Anonymous — Thu, 09 Mar 2023 21:37:48 GMT

That works! Thank you very much.

I know it is limiting to troubleshoot with a PBIX file. Hopefully, I can upload a PBIX file next time I post a problem.