YES! Public preview is finally here, and DAX UDFs have the potential to change how we write DAX in a big way. If you don’t know what UDFs are, head over to Microsoft (and SQLBI of course) and learn everything they have said about them first, no point in repeating it here. If you want to go deeper, come along!
DAX UDF vs Calc Groups? Pros and Cons
DAX UDFs means we can finally centralize code without the overhead that comes with Calculation Groups. Yes, I am a big fan of calc groups, but I must admit that they add complexity (calc group precedence anyone?) and performance issues that can’t be easily dismissed. Indeed, the fact that calc groups will affect any measure they find in their scope, forces you to «protect» the code to make sure that only the intended measures will be affected, and that is quite a PITA.
Another great thing is that User Defined functions can have more than one parameter. If you have a mesure for the value and a measure for the target now you can feed them to the function and return the deviation, deviation percentage or similar without having to repeat the code for each pair of value-target pair of measures. I love this.
There is one aspect, though, that Calc groups deliver and functions, on their own do not. Calc groups allow you to have far fewer measures on your model, «saving you time». And this is mostly true. However, having many measures per se is not that bad. What is bad is having to author them by hand. With functions, the code of the measure is short and once you have them authored, the real code is shared so managing that part is easy. But some work remains. You still need to actually create the measure in certain table, give it a name, give it a format string, maybe even a display folder. So even if you have your wonderful «Time intel» functions, creating all the time-intel measures for your base measures can get very time consuming and more important: not fun.
Can we do better?
If you follow this blog you might already imagine where this is going. Yes, C# Scripts it is! Ever since I started playing with the functions I decided a script to automatically create the measures is necessary. However, we will need to enrich our functions so that all this properties we discussed (name, display folder, format string) can be defined without asking the user every single time. Luckily for us, like the rest of objects of the tabular model, DAX UDFs can have annotations! you add a label-value pair to them and retrieve it later. This will become very very useful.
DAX UDF + Annotations + Magic ➡️ FunctionExtended
When I started playing around, I saw I would need to ask the user for each of the parameters of the function or functions selected. And that already posed a major challenge. In the tabular model, the function only has a single property where the whole expression is stored, parameters, code, everything as a string. And that’s it.
Not only that, each parameter also is not just a name, it can have a type a subtype and a parameter mode. So at some point these might become relevant for the scripts. Checking every time on the expression string did not look as a great idea. And of course we have all those annotations we mentioned earlier and maybe would be better as a property of a new object that is the function, but with extras.
But what is this FunctionExtended thing? This is something that exists only inside the C# Script. Based on the function of your model, a function extended can be generated and from there, and then the code can become much much easier.
Custom Classes in C# Scripts
This is not something you find in most scripts and actually I discovered that watching a session from Daniel Otykier (who else?) a long time ago. Indeed that’s how I implemented the GeneralFunctions Class, that allow me to reuse code in C#. If you have no idea of what I’m talking about, please go read this article where I explain the setup I use to author C# scripts. For the TL;DR type of people, just know that I can have a script that depending on some comments I add at the beginning of a method, when I copy it with a TabularEditor macro, it will copy the necessary classes at the end of the script, so that when I paste the code inside Tabular Editor, this is ready for execution. Mind blowing, but not today’s topic!
For our FunctionExtended object I created yet another project to define the class, and I called it DaxUserDefinedFunction. there I created the public class Function extended and added several properties
public class FunctionExtended
{
public string Name { get; set; }
public string Expression { get; set; }
public string Description { get; set; }
public string OutputFormatString { get; set; }
public string OutputNameTemplate { get; set; }
public string OutputType { get; set; }
public string OutputDisplayFolder { get; set; }
public Function OriginalFunction { get; set; }
public List<FunctionParameter> Parameters { get; set; }
private static List<FunctionParameter> ExtractParametersFromExpression(string expression){...}
public static FunctionExtended CreateFunctionExtended(Function function)
{
var functionExtended = new FunctionExtended
{
Name = function.Name,
Expression = function.Expression,
Description = function.Description,
Parameters = ExtractParametersFromExpression(function.Expression),
OutputFormatString = function.GetAnnotation("formatString"),
OutputNameTemplate = function.GetAnnotation("nameTemplate"),
OutputType = function.GetAnnotation("outputType"),
OutputDisplayFolder = function.GetAnnotation("displayFolder"),
OriginalFunction = function
};
return functionExtended;
}
}In this code block you see at the top the properties of the function that are transferred to this new object for convenience (Name, expression and description). Then come 4 more properties that read from the annotations fo the function (Outputformatstring, OutputNameTemplate, OutputType, OutputDisplayFolder. Then I decided that i could have the whole object function just in case I want to retrieve the whole function object, and then is the key property «Parameters». As you can see is a list so that means ordered elements, which is important for funcitions, but you see this is not a string, is a list of «FunctionParameter». And what is this? this is yet another object that we declare right after the extended function:
public class FunctionParameter
{
public string Name { get; set; }
public string Type { get; set; }
public string Subtype { get; set; }
public string ParameterMode { get; set; }
}This one is pretty straight forward. It’s all string just to be able to access each element instead of going every time to the expression string.
The special sauce in all that is the code inside «ExtractParametersFromExpression» that it’s a bit too long to include here, but you will find it on the repo. It basically extracts the different elements from the string. The good thing is that nothing is specified you can initialize the property with the default value, so that if you need that bit for your algorithm you can safely assume that the property is informed.
Actually as I’m typing this I’m already thinking on changes I’ll do to this code. What I’ll do is that if the function does not have the annotation for any of the properties (most likely since PBID does not have a UI for it beyond TMDL view) the code will ask you to inform the annotation and there it will remain so the next time if will jump straight to whatever is to be done.
Infrastructure in place, what about the actual script?
The script at this point is only able to generate measures, but it will generate as many as the cartesian product of all the different values provided for each of the parameters. If your code requires a measure a table and a column, and you provide 4 measures, 2 columns and 3 tables it will generate 4*2*3 = 24 measures!
Getting the name of the output measures right
Regarding the name, as you may have noticed, the property is called OuputNameTemplate. This means that on the string that you provide it will replace certain placeholders by values reflecting the parameter that your are using for that particular measure. Imagine your function calculates the previous year value with certain logic. If your parameter name is «baseMeasure», your measure nameTemplate annotation could be «baseMeasureName PY». During execution, when «Sales Amount» measure is being used, the output measure name will become «Sales Amount PY».
Getting the right format string
This is something that I thought I could get away lightly, but at the end I decided to implement properly. The thing is, if you think about time intel functions to generate new functions, sometimes you want to create measures with the exact same formatstring as the base measures, for example, when you calculate the previous year value. However, if you are calculating the year over year value, you want to use a «+something;-something;zeroformat» and that something is probably the formatstring of the base measure. But it could be that this base measure already has some «positive;negative;zero» formatstring.
Anyway, the way I implemented it is that you can use baseMeasureFormatStringFull as placeholder for the whole format string of the baseMeasure parameter (change baseMeasure for the name of your parameter). If you are building a 3-part formatstring yourself you want to use the baseMeasureFormatStringRoot, which is just the first bit, up until the first semicolon. Maybe not the most elegant solution, but if it works, it works. For my TimeIntel.YOY function, the parameter name is «baseMeasure» and the formatString annotation is:
+baseMeasureFormatStringRoot;-baseMeasureFormatStringRoot;-
So for my Sales Amount measure, my format string is «$ #,##0.00» and for my Sales Amount YOY measure it becomes: «+$ #,##0.00;-$ #,##0.00;-«. Sweet, isn’t it?
What about the displayfolder?
The displayfolder annotation is also a template, here we just support the basemeasureName placeholder, but maybe I’ll implement a basemeasureDisplayfolder in case you want subfolders if your base measure is already in a folder.
At this point you might be thinking:
The script is here! enjoy! It should run in both TE3 and TE2 even without the roslyn compiler configured.
Does it work? Example!
Let’s imagine I have this 4 functions (you may recall the actual DAX from daxpatterns.com which I used for the Time Intelligence Calc group back in the day I started writing c#).
-------------------------
-- Function: TimeIntel.CY
-------------------------
FUNCTION TimeIntel.CY = (baseMeasure: ANYREF) => baseMeasure
-------------------------
-- Function: TimeIntel.PY
-------------------------
FUNCTION TimeIntel.PY =
(baseMeasure: ANYREF) =>
IF(
[ShowValueForDates],
CALCULATE(
baseMeasure,
CALCULATETABLE(
DATEADD(
'Date'[Date],
-1,
YEAR
),
'Date'[DateWithSales] = TRUE
)
)
)
--------------------------
-- Function: TimeIntel.YOY
--------------------------
FUNCTION TimeIntel.YOY =
(baseMeasure: ANYREF) =>
VAR ValueCurrentPeriod = TimeIntel.CY(baseMeasure)
VAR ValuePreviousPeriod = TimeIntel.PY(baseMeasure)
VAR Result =
IF(
NOT ISBLANK( ValueCurrentPeriod )
&& NOT ISBLANK( ValuePreviousPeriod ),
ValueCurrentPeriod
- ValuePreviousPeriod
)
RETURN
Result
-----------------------------
-- Function: TimeIntel.YOYPCT
-----------------------------
FUNCTION TimeIntel.YOYPCT =
(baseMeasure: ANYREF) =>
VAR ValueCurrentPeriod = TimeIntel.CY(baseMeasure)
VAR ValuePreviousPeriod = TimeIntel.PY(baseMeasure)
VAR CurrentMinusPreviousPeriod =
IF(
NOT ISBLANK( ValueCurrentPeriod )
&& NOT ISBLANK( ValuePreviousPeriod ),
ValueCurrentPeriod
- ValuePreviousPeriod
)
VAR Result =
DIVIDE(
CurrentMinusPreviousPeriod,
ValuePreviousPeriod
)
RETURN
ResultAs you may notice, you can’t have special characters in function names, that’s why I called it TimeIntel.YOYPCT. You also see that I used this TimeIntel prefix for all of them. As of today it’s not possible to create display folders for functions so I thought this could be a good way to group functions somehow. You can use periods in the name (like they exist for native DAX functions, but you can’t then continue with a number. Things to keep in mind.
This time around with some ad-hoc scripts and some manual input I prepared the function annotations, but with the final form of the scrit you will be prompted if the annotations are not there, and then your input will be stored as anotation in case you run the macro again it will not prompt you a second time. Of course you can change the annotation manually. Here for TimeIntel.YOYPCT for example:
I highlighted here the bits that will be updated by the macro. It’s true that in different models you’ll have different date tables and the columns used for the calculation may have different names, but this does not mean that you should pass them as parameters. Basically you may need to build your functions with a C# script like we have done with Calc Groups in the past and customize the little bits that may change from model to model.
But is this for real?
Judge for yourself!!