Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
Time =
var _Minutes='TABLE'[Column1]
var _Hours=int( _Minutes/60)
var _RemMinutes=MOD(_Minutes, 60)
return
_Hours&":"&IF(_RemMinutes< 10,"0"&_RemMinutes,_RemMinutes)
Solved! Go to 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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |