Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I need help with converting duration values in text (xx day XX hour XX minutes) to decimals (hhhh.mm) using DAX.
Appreciate any help guys...
Solved! Go to Solution.
@Nelvbautista If you truly want a DAX solution, try this:
Column =
VAR __Duration = [Ticket Duration]
VAR __DayLocation = SEARCH("day",__Duration,,0)
VAR __HourLocation = SEARCH("hour",__Duration,,0)
VAR __MinuteLocation = SEARCH("minute",__Duration,,0)
VAR __Days = IF(__DayLocation,VALUE(LEFT(__Duration,__DayLocation - 1)),0)
VAR __DaySeparator = IF(__DayLocation<>0,SEARCH(" ",__Duration,__DayLocation,0),1)
VAR __HourSeparator =
SWITCH(TRUE(),
__DayLocation<>0,SEARCH(" ",__Duration,__DaySeparator+1,1),
__HourLocation=0,1,
SEARCH(" ",__Duration)
)
VAR __Hours = IF(__HourLocation=0,0,VALUE(MID(__Duration,__DaySeparator,__HourSeparator - __DaySeparator + 1)))
VAR __MinuteSeparator = IF(__HourSeparator=1,1,SEARCH(" ",__Duration,__HourSeparator+1,0))
VAR __LastSeparator = SEARCH(" ",__Duration,__MinuteSeparator+1,0)
VAR __Minutes = VALUE(MID(__Duration,__MinuteSeparator,__LastSeparator - __MinuteSeparator + 1))
RETURN
__Days * 24 + __Hours + __Minutes/100
@Nelvbautista If you truly want a DAX solution, try this:
Column =
VAR __Duration = [Ticket Duration]
VAR __DayLocation = SEARCH("day",__Duration,,0)
VAR __HourLocation = SEARCH("hour",__Duration,,0)
VAR __MinuteLocation = SEARCH("minute",__Duration,,0)
VAR __Days = IF(__DayLocation,VALUE(LEFT(__Duration,__DayLocation - 1)),0)
VAR __DaySeparator = IF(__DayLocation<>0,SEARCH(" ",__Duration,__DayLocation,0),1)
VAR __HourSeparator =
SWITCH(TRUE(),
__DayLocation<>0,SEARCH(" ",__Duration,__DaySeparator+1,1),
__HourLocation=0,1,
SEARCH(" ",__Duration)
)
VAR __Hours = IF(__HourLocation=0,0,VALUE(MID(__Duration,__DaySeparator,__HourSeparator - __DaySeparator + 1)))
VAR __MinuteSeparator = IF(__HourSeparator=1,1,SEARCH(" ",__Duration,__HourSeparator+1,0))
VAR __LastSeparator = SEARCH(" ",__Duration,__MinuteSeparator+1,0)
VAR __Minutes = VALUE(MID(__Duration,__MinuteSeparator,__LastSeparator - __MinuteSeparator + 1))
RETURN
__Days * 24 + __Hours + __Minutes/100
@Greg_Deckler appreciate your help on this a couple of weeks back. I'm now getting an error, "An argument of function 'MID' has the wrong data type or has an invalid value" when I expended the table to include historical data.
Below is the updated duration column (blanks are 0 duration). Appreciate any help you can provide:
@Nelvbautista I made an improved version here: Text Duration Conversion - Microsoft Power BI Community
Also, in the comments, note that there is an alternate (better) way to do the text 2 table bit.
Hi @Greg_Deckler,
As expected, really appreciate the speedy response. I tried (copy-pasting) the updated formula and only returned 900 on all rows.
I was also prompetd to update to the latest Power BI Desktop version when trying to open your pbix attachment. My current version is "2.93.981.0 64-bit (May 2021)". I might need to wait for our IT for this update first. Again, appreciate the help.
Nel
@Nelvbautista Glad to hear it because that one took a bit of time! You could likely convert that formula to Power Query if you were so inclined using the function equivalents for SEARCH, LEFT, MID, etc. in Power Query.
@amitchandak @community_pinki appreciate the assistance and time in looking into this, but would there be an option to have this as a formula because this will be a growing dataset wherein new rows will be continously added?
Hey @Nelvbautista ,
Follow below steps it will work :-
Step 1 : Go to the Power Query Editor --> Click on the Duration Column and apply some transformation like "lowercase" and "trim"
Step 2 : To extract the day from column go to the Add Column --> Extract --> Click on text before delimeter --> text before delimeter popup will open
--> Specify Delimeter as "day" and in the advanced option --> select "From the end of the input" from Scan for delimter dropdown.
It will create new column rename it with Day.
Step 3: Add Column --> Extract --> now select for "Text between delimeter"
It will open the popup for Text between delimeter --> Spcify Start delimter as press space button and then type hour
--> Spcify End delimter as press space button
--> In the Advanced option select "From the end of the input" from Scan for start delimeter dropdown.
--> select "From the start delimeter, toward the start of the input"
--> Click OK.
Step 4: Follow the 3rd step for minutes and seconds
Step 5:Rename all the column as Day, Hour, Minutes, seconds respectively.
Step 6: Copy all four columns Goto the Tranform-->Replace values --> Values to Find--> leave it blank -->Specify "Replace with" as 0--> Click OK.
Step 7 : Copy all fourcolumns Again Goto the transform --> Merge Column -->Seperator: colon-->Specify column Name-->Click OK
Step 8: Right click on the column and change the data type as "Duration"
Thanks...
@Nelvbautista , Try a new column like
Number.FromText (Text.Combine(List.RemoveItems(Text.Split([ticket duration], " "), {"hour", "minutes", "day"} ),"."))
User | Count |
---|---|
52 | |
51 | |
20 | |
17 | |
16 |
User | Count |
---|---|
113 | |
46 | |
44 | |
28 | |
22 |