Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I needed some assistance. I have gone through the forums but cant seem to find an answer.
I have a measure called run time, which gives me numerical values like 78555,9875,etc.
What I want to do is convert and format this measure to display as ' X' Days 'Y' Hours
For example if run time is 90,000. It should display as : 1 Day 1 Hours
Using simple math to do this conversion where in one day has 86,400 seconds and an hour has 3,600 seconds.
Please help!! I am stuck at a work project and have spent 2 days trying to figure this out.
Solved! Go to Solution.
See if this article helps. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486. But you probably want something along the lines of (as a column):
Column = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT([Value]/__secondsInDay) VAR __hours = INT(([Value]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Hi @Anonymous,
Rather than create a calculated column, you could new a measure instead still with above formula.
Measure = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay) VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Best regards,
Yuliana Gu
See if this article helps. https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486. But you probably want something along the lines of (as a column):
Column = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT([Value]/__secondsInDay) VAR __hours = INT(([Value]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Hi @Greg_Deckler , how would you do this in dax if you were to include minutes and seconds as well? So, day, hour, minute, second.
Best,
Nils
Thank You so Much Greg! It does what I want to do but it seems I under estimated the complexity of my end goal. I have a bar chart with the run time in seconds where I wanted to feed this into. I have scaled the bar chart by dividing the run time by 86400, so that it gives me a scale in days(be it decimal), But if I try to put this calculated column as a tooltip it wont give me the days and hours after summing all the run times on that day.
Any workaround for that? I'm thinking of changing the calculation so that instead of taking the 'enabled' measure, I have to create a new measure(sum of all runtimes for that particular date) which feeds into the below code.
Column 5 = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay) VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Hi @Anonymous,
Rather than create a calculated column, you could new a measure instead still with above formula.
Measure = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay) VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Best regards,
Yuliana Gu
Thank You so Much Greg! It does what I want to do but it seems I under estimated the complexity of my end goal. I have a bar chart with the run time in seconds where I wanted to feed this into. I have scaled the bar chart by dividing the run time by 86400, so that it gives me a scale in days(be it decimal), But if I try to put this calculated column as a tooltip it wont give me the days and hours after summing all the run times on that day.
Any workaround for that? I'm thinking of changing the calculation so that instead of taking the 'enabled' measure, I have to create a new measure(sum of all runtimes for that particular date) which feeds into the below code.
Column 5 = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT('arms_reach vw_mold_tracking'[Enabled]/__secondsInDay) VAR __hours = INT(([Enabled]-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
Right, so you want the measure form, which should be something like:
Column = VAR __secondsInDay = 86400 VAR __secondsInHour = 3600 VAR __days = INT(SUM([Value])/__secondsInDay) VAR __hours = INT((SUM([Value])-(__days*__secondsInDay))/__secondsInHour) RETURN __days & " Days " & __hours & " Hours"
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
101 | |
92 | |
74 | |
60 | |
59 |