Convert minutes column (in whole number format) to hh:mm (number format)

[Humanoidhuman]

Hi,

Background: I have a column in my Power BI (cube dataset) which is in 'whole number' format and displays the time in minutes. I am looking to convert this column to display hours and minutes (hh:mm) in a number format so I can select the 'between' slicer option to display on my visuals. 

Problem: I successfully got the format I needed using the following two DAX function to create a new column 

Time = FORMAT('TABLE'[Column1] / 60 / 24, "h:mm")

and 

Time =
var _Minutes='TABLE'[Column1]
var _Hours=int( _Minutes/60)
var _RemMinutes=MOD(_Minutes, 60)
return
_Hours&":"&IF(_RemMinutes< 10,"0"&_RemMinutes,_RemMinutes)

However, the column displays the results in a text format, for which I am unable to select the 'between' slicer option. 

Anticipated Solution: I want to the resulting column to be preferably in a number format so that it lets me select the 'between' slicer option. 

TIA!

[v-yohua-msft · Accepted solution]

Hi, @Humanoidhuman 

You want to convert the time (in minutes) to the format of hours and minutes (hh:mm) and still keep it as a number so that you can use the "between" slicer option in Power BI.

However, the "between" slicer option in Power BI is only available for numeric data types. When you convert minutes to the format hh:mm, it becomes a text/string data type, which is why you can't use the "between" slicer option.

The workaround is to convert time (in minutes) to a decimal number representing hours. For example, 90 minutes would be represented as 1.5 (1 hour and 30 minutes):

Time = 'TABLE'[Column1] / 60

This way, you can still use the Between slicer option.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Humanoidhuman]

@v-yohua-msft Thank you so much for that! 
I marked your answer as solution as it helped me get to where I wanted for the purpose of my report. However, I would just like to add that this function will convert the duration into hours format, not hh:mm. E.g. this will convert 90 minutes to '1.5' as opposed to 1:30.