DAX UDFs are here and as anticipated they are indeed revolutionizing the way we write DAX. Indeed together with the introduction of DAX UDFs, «the italians» presented «daxlib.org» a common repository for sharing Model-independent DAX UDFs. I’m also contributing there and I’ve seen how many commits and pull requests are going on and it’s quite amazing. All those functions, though, are –again– model-independent. Which is great, of course. But sometimes we want to make functions that are quite similar, but not quite the same. They have some dependencies on the model, but achieve the same kind of transformation. Just like calculation groups. What is to be done then? Well, we can always write a script that will customize then for us on run-time. Just like we did with calculation groups!

At this point you might be thinking…

and well, you might not be entirely wrong. It’s pretty much the same story, with the added twist that the functions created will be ready for the macro introduced in the previous post, meaning that you can automatically generate measures with these functions with a reasonable name, format string, display folder and –last but not least– in a reasonable table too.

Coding environment

As usual we’ll be using my Visual Studio (or VS Code if you want) to author scripts. If you want to check it out, clone it from here and build the whole solution to download dependencies. To configure the macro that will come pick up the code from this files and put into the clipboard for you to paste and execute in Tabular editor, check out this video or this and this article. The code has evolved a little, but you will get it.

Classic use case to centralize code

Even though that Custom Calendars are now all the rage, I thought it would be good to have the logic of the good old Standard Calendar calculations of Daxpatterns.com also as DAX functions. Unlike Calc groups this time around I can reuse one calculation in another so it will be more compact overall. Also since the code will be already inside a measure, I don’t have to create any auxiliar table to store the names of the measures that should be affected by the calculation group like I did for the calc group. And no need for dynamic format string expressions. Each measure will have the right format string and that’s it.

Encapsulate all the things!

The logic of the calculations in daxpatters.com uses an extra calculated column in the date table and measure to determine if a data point is to be shown or not. In order to build these you will need to know where your date table and date column are, as well as the fact table and its main date column. The date table and extra column and measure also show up in the time intel expressions as well. That’s why here we are talking about a model-dependent DAX UDFs.

The logic is the same of the time intel calc group that I published a long time ago (based as well in daxpatterns.com). Every time I check the code for the time intel calc group I’m quite embarrassed on how badly written it is (this was way before moving authoring to Visual Studio), so I’ll take this opportunity to build something better. I figured out (rightly or wrongly) that in the future I might want to be able to easily get the date table or fact table, so I created functions for that. These functions will try to determine candidate tables and if only one is found they will return it, so no interaction is requested from the user. When more than one candidate is found then will ask the user to choose between them. And if no candidate at all is found then will ask the user to choose from the full list of tables. It’s not super complex code, but if you have it on your main script it makes it harder to read. Once everything is encapsulated it looks deceptively easy:

Table dateTable = Fx.GetDateTable(model: Model);
if (dateTable == null) return;

Column dateColumn = Fx.GetDateColumn(dateTable);
if (dateColumn == null) return;

Table factTable = Fx.GetFactTable(model: Model);
if (factTable == null) return;

To get the main date column of the fact table I first get all the date columns of the table. And at this point I could make the user choose if more than one is found. But we can go one more step and preselect the column that has an active relationship with the date table, which is most likely to be the one we want to use to determine if a data point should be shown or not.

Enumerable<Column> factTableDateColumns =
    factTable.Columns.Where(
        c => c.DataType == DataType.DateTime); 

if(factTableDateColumns.Count() == 0)
{
    Error("No Date columns found in fact table " + factTable.Name);
    return;
}

if(factTableDateColumns.Count() == 1)
{
    factTableDateColumn = factTableDateColumns.First();
}
else
{
    factTableDateColumn = factTableDateColumns.First(
        c=> Model.Relationships.Any(
            r => ((r.FromColumn == dateColumn && r.ToColumn == c)
                || (r.ToColumn == dateColumn && r.FromColumn == c)
                    && r.IsActive)));

    factTableDateColumn = SelectColumn(factTableDateColumns, factTableDateColumn, "Select main date column from the fact table"); 
}
if(factTableDateColumn == null) return;

As you can see we initialize factTableDateColumn with the one that has the relationship, but just to provide it as the preselect parameter in the SelectColumn statement. This decreases friction when using the macro.

Now we can create the extra column and measure in a very clean way:

string dateTableAuxColumnName = "DateWith" + factTable.Name.Replace(" ", "");
string dateTableAuxColumnExpression = String.Format(@"{0} <= MAX({1})", dateColumn.DaxObjectFullName, factTableDateColumn.DaxObjectFullName);

CalculatedColumn dateTableAuxColumn = dateTable.AddCalculatedColumn(dateTableAuxColumnName, dateTableAuxColumnExpression);
dateTableAuxColumn.FormatDax(); 

dateTableAuxColumn.IsHidden = true;

string dateTableAuxMeasureName = "ShowValueForDates";
string dateTableAuxMeasureExpression =
    String.Format(
        @"VAR LastDateWithData =
            CALCULATE (
                MAX ( {0} ),
                REMOVEFILTERS ()
            )
        VAR FirstDateVisible =
            MIN ( {1} )
        VAR Result =
            FirstDateVisible <= LastDateWithData
        RETURN
            Result",
        factTableDateColumn.DaxObjectFullName,
        dateColumn.DaxObjectFullName);

Measure dateTableAuxMeasure = dateTable.AddMeasure(dateTableAuxMeasureName, dateTableAuxMeasureExpression);
dateTableAuxMeasure.IsHidden = true;
dateTableAuxMeasure.FormatDax();

BTW, if you want to use FormatDax() is important to use CalculatedColumn as variable type instead of Column! (I will deny I opened an issue on that)

Build the functions already!

Ok, now that the pieces are in place we can build the functions. You will see that some don’t need any customization but depend on functions that do depend on the model. Also I tried to follow the naming conventions for functions outlined by SQLBI guys
(Update: Well as a matter of fact «Model» can’t be used either! more on that at the end of this article)

//CY --just for the sake of completion 
string CYfunctionName = "Model.TimeIntel.CY";
string CYfunctionExpression = "(baseMeasure) => baseMeasure";

Function CYfunction = Model.AddFunction(CYfunctionName);
CYfunction.Expression = CYfunctionExpression;
CYfunction.FormatDax();

CYfunction.SetAnnotation("displayFolder", "baseMeasureName TimeIntel");
CYfunction.SetAnnotation("formatString", "baseMeasureFormatStringFull");
CYfunction.SetAnnotation("outputType", "Measure");
CYfunction.SetAnnotation("nameTemplate", "baseMeasureName CY");
CYfunction.SetAnnotation("destination", "baseMeasureTable");

OK, this one was the «do nothing function» 😀 but since we wanted to recreate the time intel calc group with functions I added this one for the sake of completion. Here the important part are the annotations that will define the measures created with this function. Note that for FormatString you can use «FormatStringFull» which will include the three parts of the format string if one such format string is in place and FormatStringRoot which will just take the very first bit. We’ll see the application in a minute.

Oh and I’m not using this time the «baseMeasureDisplayFolder» placeholder but it would indeed return the display folder of the base measure that you choose when generating measures with this function. (Well in the final version of the script I am indeed using it)

Finally you have probably realized this Model.TimeIntel.CY is a bit long as a function name, but it’s actually how SQLBI recommends to name measure dependent functions. This one in particular is not model dependent, but I want to group it with others that will, like the following one.

//PY

string PYfunctionName = "Model.TimeIntel.PY";
string PYfunctionExpression = 
    String.Format(
        @"(baseMeasure: ANYREF) =>
        IF(
            {0},
            CALCULATE(         
                baseMeasure,
                CALCULATETABLE(
                    DATEADD(
                        {1},
                        -1,
                        YEAR
                    ),
                    {2} = TRUE
                )
            )
        )",
        dateTableAuxMeasure.DaxObjectFullName,
        dateColumn.DaxObjectFullName,
        dateTableAuxColumn.DaxObjectFullName);

Function PYfunction = Model.AddFunction(PYfunctionName);
PYfunction.Expression = PYfunctionExpression;
PYfunction.FormatDax();

PYfunction.SetAnnotation("displayFolder", "baseMeasureName TimeIntel");
PYfunction.SetAnnotation("formatString", "baseMeasureFormatStringFull");
PYfunction.SetAnnotation("outputType", "Measure");
PYfunction.SetAnnotation("nameTemplate", "baseMeasureName PY");
PYfunction.SetAnnotation("destination", "baseMeasureTable");

For this one you can already spot that this is a shameless take on the daxpatters.com logic. This logic avoids showing previous year values for data points in the future, and for periods that have not yet completed (like the whole current year) it will calculate only the proportional part of previous year as well.

As for the annotations you see we use the same format string and display folder so that all the measures generated will go together with some standard naming.

//YOY
string YOYfunctionName = "Model.TimeIntel.YOY";
string YOYfunctionExpression =
    @"(baseMeasure: ANYREF) =>
    VAR ValueCurrentPeriod = Model.TimeIntel.CY(baseMeasure)
    VAR ValuePreviousPeriod = Model.TimeIntel.PY(baseMeasure)
    VAR Result =
	    IF(
		    NOT ISBLANK( ValueCurrentPeriod )
			    && NOT ISBLANK( ValuePreviousPeriod ),
		    ValueCurrentPeriod
			    - ValuePreviousPeriod
	    )
    RETURN
	    Result";

Function YOYfunction = Model.AddFunction(YOYfunctionName);
YOYfunction.Expression = YOYfunctionExpression;
YOYfunction.FormatDax();

YOYfunction.SetAnnotation("displayFolder", "baseMeasureName TimeIntel");
YOYfunction.SetAnnotation("formatString", "+baseMeasureFormatStringRoot;-baseMeasureStringRoot;-");
YOYfunction.SetAnnotation("outputType", "Measure");
YOYfunction.SetAnnotation("nameTemplate", "baseMeasureName YOY");
YOYfunction.SetAnnotation("destination", "baseMeasureTable");

this one is interesting because there are no String.Format as the expressions will be exactly the same for any model, but of course you can see it is calling the two functions defined previously. And yes, here we are using the baseMeasureFormatStringRoot we mentioned earler to add this + sign in front of positive numbers to make it clear that we are talking about a change and not a total magnitude.

Let’s do just one more:

//YOY%
string YOYPfunctionName = "Model.TimeIntel.YOYPCT";
string YOYPfunctionExpression =
    @"(baseMeasure: ANYREF) =>
    VAR ValueCurrentPeriod = Model.TimeIntel.CY(baseMeasure)
    VAR ValuePreviousPeriod = Model.TimeIntel.PY(baseMeasure)
    VAR CurrentMinusPreviousPeriod =
        IF(
            NOT ISBLANK( ValueCurrentPeriod )
                && NOT ISBLANK( ValuePreviousPeriod ),
            ValueCurrentPeriod
                - ValuePreviousPeriod
        )
    VAR Result =
        DIVIDE(
            CurrentMinusPreviousPeriod,
            ValuePreviousPeriod
        )
    RETURN
        Result";
Function YOYPfunction = Model.AddFunction(YOYPfunctionName);
YOYPfunction.Expression = YOYPfunctionExpression;
YOYPfunction.FormatDax();

YOYPfunction.SetAnnotation("displayFolder", "baseMeasureName TimeIntel");
YOYPfunction.SetAnnotation("formatString", "+0.0%;-0.0%;-");
YOYPfunction.SetAnnotation("outputType", "Measure");
YOYPfunction.SetAnnotation("nameTemplate", "baseMeasureName YOY%");
YOYPfunction.SetAnnotation("destination", "baseMeasureTable");

Here one bit is that the function is called Model.TimeIntel.YOYPCT because you can’t use special characters like % in the function name. Also as you can see for format string you can set up an arbitrary format string too if you don’t want to use the one of the parameter at all. And yes, of course you can use % in the name of the measures generated using this function.

Wait, what superpower do I get again?

with this script in your toolbelt (i.e. in your Tabular Editor installation) you can land in any semantic model and install these model-dependent functions in no time, but not only that, with this other script you can then select them and specify which base measures you want to use. This will create a display folder for each of them and will put all the function-based time intel measures inside with nice names and nice format strings! Not bad huh?

You probably don’t believe me or you just scroll too fast for reading, so here’s the video:

Just to be clear, here in a brand new model with no special properties or anything like that we run two scripts:

• Create Time Intel Functions <– object of this blog post
• Create Measures from Functions <– object from my previous post (and already improved!)

The code is already using one more annotation from the ones described earlier. It’s «outputDestination» where you can use baseMeasureTable or the table of any other column/measure parameter, or even just a the actual table parameter you are feeding into a function. In this case though it was quite straight forward as we just have one measure. For the displayfolder I used baseMeasureDisplayFolder\baseMeasureName TimeIntel so the script will create the time intel folder wherever the baseMeasure is located.

Oh and last but not least, the script will increase the compatibility level of your model to 1702 if it’s lower so that DAX UDFs can even be created. It’s quite nice that even this property is now within reach of external tools like Power BI without enabling unsupported actions.

And that’s pretty much it, thanks for reading and I hope this will inspire you to industrialize your model-dependant Dax functions too.

Model-independent functions can enriched too!

BY THE WAY! The functions you share on daxlib.org (which are model-independent!) can also be «enriched» to work with the script to create measures. If your are unsure on how to do that, just run the measure-creating macro on your functions and those annotations will be added during runtime. Then later if you don’t like them you can change them easily with Tabular Editor or even TMDL view.

As a matter of fact, I have already shared a couple functions that do just that. You have your «Actual» measure and your «Target» measure, but of course you need your absolute and relative deviation for target measures, right? Well now in a couple of clicks you’ll have your measures with a decent name, decent display folder, right next to the «Actual» measure and with the right format string! Check them out here!

Be careful with the naming!

As much as I have discussed the naming conventions for functions I used Model throughout this article. Well, it turns out you cannot use that word either. I thought I recalled that this was the recommended prefix for model-dependent functions, but it turns out that it is not «Model» but rather «Local». There’s no way I’m redoing all the screenshots and video. As you saw, you can create functions that will fail when called because of the name. Hopefully in the future this will be blocked on function creation time. Until then, BE CAREFUL WITH NAMING!

Closing words

Well, long article to say: if the functions are model-independent, make sure to share on Daxlib.org. If they are model-dependant, consider if they are worth a script like the one we saw today and share it with the world if you can or those who can benefit from it in your organization.

Thanks for reading all the way to the end!
Remember to follow the conversation on Bluesky and LinkedIn!