Forum Discussion
Create a function in DAX?
I have a table with about 20 measures displaying a number which is the number of seconds. E.g. 19,000 which means 5 hours 16 minutes and 40 seconds. I can edit each measure and add the logic to do this conversion. But it would be nice to create a function which converts and number into HH:MM:SS format, then simply reference this function in each measure.
How can this be done?
5 Replies
Unfortunately, you can't define functions in DAX. (There are some clunky workarounds like defining a parameter table to use as the domain of the function and using filters on that table to pass values into the function but that's not feasible for large domains.)
Fortunately, the code to format is simple. Power BI interprets numbers as durations in days, so you can format N_Seconds in HH:MM:SS as
FORMAT(N_Seconds/60/60/24, "HH:MM:SS")which isn't much more verbose than calling a separate function anyway.
Another possibility would be to try defining a calculation group where you create a calculation item like this:
FORMAT ( SELECTEDMEASURE()/86400, "HH:MM:SS" )Anonymous , You can create a function, that is in M, and that can be used in power query -https://radacad.com/custom-functions-made-easy-in-power-bi-desktop
as of now a code like this need to added in all formula
quotient([Value],3600) & ":" & quotient(mod([Value],3600),60) & ":" mod(mod([Value],3600),60)
Custom (Reusable) DAX Function please vote :}
Hello Anonymous ,
You can refer this,
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Yes, but how do I write 20 measures that simply refer to the [Duration] measure without repeating the logic 20 times?
I'm after something like this:
[Duration in HHMMSS](@seconds) = ** logic to convert seconds to HH:MM:SS **
[Time 1 (hh:mm:ss] = [Duration in HHMMSS]([Time 1 (s)])
[Time 2 (hh:mm:ss] = [Duration in HHMMSS]([Time 2 (s)])
[Time 3 (hh:mm:ss] = [Duration in HHMMSS]([Time 3 (s)])
etc.
