Omaer Khan on 01 Mar 2017 02:06:50
I'd like to be able to create reusable functions in DAX, instead of using the same DAX code in multiple measures.
Example: I have some complicated DAX code, which calculates the no of working seconds between two date-times. This excludes holidays, weekends, and only counts seconds from the start of the work-day, to the end of the work-day.
I use this DAX code a lot, for many measures which need to be in "Working Time" (working hours/minutes).
I would love to not have to copy-paste a bunch of code into all of my measures, but to be able to create a reusable function instead.
- Comments (59)
- Merged Idea (1)
RE: Custom (Reusable) DAX Function
That will be very helpful, if combined with:
https://ideas.powerbi.com/ideas/idea/?ideaid=15bd4fbe-829a-4ae2-b093-937b64cf790c
(I would even want to have a choice to write visual-level function that applies to any measures in a table/matrix so I don't need to write them column by column)
I have dataset that return data where aggregation is already done for all hierarchical levels by the data source, because the aggregation cannot be done by just summing or weighted averaging constituents in a matrix. To have any visual to pick value from the right hierarchy level, I have to write measures that does something like:
var level8Value = CALCULATE(Database[values], [Level]=8)
...
return
SWITCH(
TRUE,
ISINSCOPE(Database[LEVEL8]), level8Value,
...
ISINSCOPE(Database[LEVEL1]), level1Value,
BLANK()
)
For 50 to 60 measures.
If any modification to calculation is needed that would be a maintenance nightmare. Really wish we can have a way to abstract out these logic into a reusable user defined function that supports columns as valid inputs.
RE: Custom (Reusable) DAX Function
Please ensure UDFs to allow all sorts of logic, on a par with built-in functions where possible.
For example, my client has a custom calendar driven by a finance periods table in a business system, which may have periods as 4-4-5 week periods rather than calendar months. All my "previous month" measures have to use ugly, repeated code which is (a) repetitive, (b) hard to write, (c) hard to read.
I would love to have the power to be able to create a UDF, say PreviousFinancePeriod, on an equal status with PREVIOUSMONTH() which means I could then create measures like
WIP B/F = calculate ([WIP], PreviousFinancePeriod (Dates[Date]))
RE: Custom (Reusable) DAX Function
We have metrics that couldn't be aggregated with sum, weighted average, etc when fed into a matrix with more than 1 level of hierarchy, while upstream data source has done the calculation and fed all hierarchy of data into a single table. Have been relying on Measures to apply logic to a column so that aggregation at each hierarchical level will pick the right level directly from data source, but we have to create these measures for each column one by one. Would be nice if we can create a custom DAX function that takes a column or even a table as parameter, so the logic can apply to the column or all columns in a table, that will reduce a lot of boilerplate DAX code.
RE: Custom (Reusable) DAX Function
Must have feature.
RE: Custom (Reusable) DAX Function
I faced the same problem. I really need to be able to keep common code in a function, parameterize it and use it across all my measures.
RE: Custom (Reusable) DAX Function
I am calculating a time period based on current date and a product attribute of lead time. Without a custom DAX function that can accept parameters, I guess the next best solution is adding a calculated column. Would love the option of a custom DAX function, for such a case.
RE: Custom (Reusable) DAX Function
Please add this! It would save so much time.
RE: Custom (Reusable) DAX Function
I would love to have this function, too. Especially around passing arguments to these functions would be "very" helpful and would declutter a lot. Recent example, I had to do the same non-standard comparison calculation for 5 different product brands --> 5 new measures. one functions and 5 ways to define arguments would help.
RE: Custom (Reusable) DAX Function
..tumbleweed... I genuinely wonder what the PowerBi team is working on sometimes when features like this get ignored for years.
RE: Custom (Reusable) DAX Function
Must have feature. Would be amazing to have the ability to define reusable functions in DAX.