Blog

Activating inactive relationships the smart way

Hi, today I want to talk about inactive relationships. Those relationships with the dashed line that unless you invoque them with USERELATIONSHIP they do nothing. The thing is a common pattern if you read this blog — let’s say you have a retail model, and for some charts you need to use the order date and in other charts the delivery date. Other than that measures are the same, such as Sales amount,  total cost, margin etc. Well, you could do a copy of each of the measures and wrap it in a CALCULATE( … USERELATIONSHIP(… ) ) or do a calculation group.  Today we’ll do that, with style.

Today I took the contoso model and added a couple of bogus columns to make my point. One is the delivery date (just a few days after order date) and the other is a «Reference Storekey» something like the key of the store that for some reason also owns the sale. I just took the last digit of the key, so there are 10 reference stores. Whatever. The point here is that I wanted to create inactive relationships, and that’s what I did.

If you only have one inactive relationship in your model –and you just want to get the job done– probably you are better off by creating a calcuation group by hand that looks like

-----------------------------
-- Calculation Group: 'Model'
-----------------------------
CALCULATIONGROUP 'Model'[Model]
    Precedence = 1

    CALCULATIONITEM "'Sales'[Reference StoreKey] - 'Store'[StoreKey]" =
        CALCULATE (
            SELECTEDMEASURE (),
            USERELATIONSHIP ( 'Sales'[Reference StoreKey], 'Store'[StoreKey] )
        )

When you apply this calculation item to a visual or a page, the model that rules all measures (no filtering here) will be with the inactive relationship.

However, you’ll agree with me that writting this calculation group is boring. I mean, the fields are already defined by the relationship, we are adding 0 value. Also imagine that suddently you have a model with 20 inactive relationships and all need to become active at some point or another. Wouldn’t it be great if we could just right-click on an inactive relationship and create a calculation group like the one above? What about rightclicking on 20 selected inactive relationships?

Ok, maybe it’s not on the top of the list of your priorities, but hey, I thought it would be cool to create such a script, so here we go.

And to makes things cooler, this time I did it with a in-script custom classes.

When I started my quest for a repository of custom functions for my script, I thought that the only way to do it was through a custom dll.  That path is very cool, but not very convenient. There are problems in which the file remains blocked, but maybe even worse you have now to distribute two files, make poeple bind them together, basically a pain. So then one day my friend Ricardo Rincón passed me the link to this video from Daniel Otykier where he goes deep into the c# programming possibilities and explains that you can actually add classes inside the script! And that’s awesome. The trick is to close a curly bracket at the end of your script, then write your classes and then leave an open curly bracket at the end!

The only little drawback is that in TE3 this curly bracket thing must be removed as the code is compiled differently. Or so I was told. But well, it’s not too bad.

UPDATE (2022-10-28): Starting on TE 2.17.2 the syntax of TE2 and TE3 is the same and the curly bracket hack is a thing of the past! Just put your class at the end of the script and you’ll be fine!

Anyway, during my attempt to build a usefull dll for my scripts, I did develop quite a few methods to deal with measure, table and calculation group creation. In all these cases, I like to give the user a default name for each thing but the opportunity to change it if he or she wants to, during run-time, so that the macro can be stored as a Macro (no more Custom Actions! this naming has been phased out from the UI in TE2). So anyway, when you ask the user for a name, the user may give you a name that cannot be used because there’s already another table with the same name for example. Then the right thing to do is not crash and try again but rather tell the user what is going on and let him or her choose a better name and continue the execution. But that’s already quite a lot of code. If you put that in the middle of your code it will make your code hard to follow. Also you might have to create several measures and tables, so you really want to package that somewhere else.

When I did all those methods I followed best practice of separating each class in a different file as I was told by the c# expert at Esbrina, Xavi Paterna. However for this new way of including the class I needed it all together so that’s what I did. I put all those classes in the same file so I could copy paste it all easily at the end of my script to have all the functionality. This approach makes the script easier to read, more customizable without making a mess with the code.

This is a similar approach of what I did with Excel Macros — I had some generic code modules I would copy to different excel workbooks, and there I could even update them or publish back to the (local) repository — I have not reached that point yet, but we’re getting closer!

using System.Windows.Forms; 
using Microsoft.VisualBasic;

//initialize calc group variable
CalculationGroupTable cg = null as CalculationGroupTable;

//create calc group (only first time)
if(!CalculationGroupUtils.CreateCalculationGroup(
    model:Model,
    myCalculationGroup: out cg,
    defaultCalculationGroupName: "Model",annotationValue:"Model modifications") return;

//goes through selected relationships
foreach (SingleColumnRelationship r in Selected.SingleColumnRelationships)
{
    //and only for those inactive
    if (!r.IsActive)
    {   
        //prepare name and expression for the calculation item
        string calcItemName = r.FromColumn.DaxObjectFullName + " - " + r.ToColumn.DaxObjectFullName;
        string calcItemExpression =
            String.Format(
                "CALCULATE(SELECTEDMEASURE(),USERELATIONSHIP({0},{1}))",
                r.FromColumn.DaxObjectFullName,
                r.ToColumn.DaxObjectFullName
            );
        
        //add calculation item
        CalculationItem calcItem = null as CalculationItem; 
        if(!CalculationGroupUtils.CreateCalculationItem(
            cg: cg,
            out calcItem,
            defaultCalculationItemName: calcItemName,
            promptUser: false, 
            calcItemExpression:calcItemExpression,
            regenerateIfPresent: false)) return;
    }
}

These are less than 40 lines of code but after that 850 lines of classes take care of all these verifications and check types etc. Also the calculation group is created with an anotation, so the second time you run the script (in case you have a new inactive relationship in the model and you want the correponding calc item) the script does not ask you about the calculation group, it goes ahead and adds the calculation item to the same calc group, whatever the name is. It finds it through the annotation! As you can see the functions have plenty of parameters, and even more I didn’t use for this example, and all have a default value, so you can set them or leave the default value. For instance, for the calculation item, I choose not to ask the user about the name, but also the last argument says that if the calc item is already there (with the same name — calc Items do not have annotations!) do not regenerate it — in case the user has done some modifications on it. If you want it new you can always delete it and run the script again right? But anyway, the point is that you can fine tune what you want, but the code is always the same. And if you want the function to do one more trick you just need to go into the class, code it once and enjoy it forever in all the scripts you do.

Yes, there is some bloat, and you need all the references used in your classes even if not used in your paticular script. But it’s convenient, really convenient. And hey, it’s just some code right?

Oh one more little thing about how this is programmed.  In order to prevent unhandeled errors to appear, I follow the code pattern that all funtions return true if things went as expected or false otherwise. that’s why all the calls to functions are inside an if statement and preceded with a exclamation mark «!» which in c# speak is «no». So if it returns false, the combination is true and then it goes into the if which is just «return» so stopping the macro execution there. Any relevant meassage error has already been handled by the function so no need to code that on the main code part.

Well, that was a LOT of talking — let’s see if the script actually works!

Pretty cool, huh?

The good things is that it has all the cool functionality (Calculation group identification by annotation, custom calc group name, no-replacement if calc item found) without any of the messy part of actually typing all that in the main code.

One thing I realized while preparing this article is that TE2 is much more picky on how you write your code than TE3, probably because of the C# version and compiler. Even some suggestions coming from Visual Studio, ( «nameof(variable)» something used in error messages) are not recognized in TE2 but accepted in TE3. Also when a method has a out argument, in TE2 this must come the first, not TE3. Also if you start naming parameters, you need to continue until the end, which makes sense but for MessageBox.Show (something from Visual Basic) there is an argument called buttons where you keep adding parameters describing what buttons should be shown, so I don’t know how to name that. Anyway, in that case I removed all the names of the parameters and that did the trick. Oh and «Variable is null» does not work, you have to type «Variable==null» ! I write this here so hopefully next time I will remember.

Also, if you are using Microsoft.VisualBasic — you need to call the reference first! Even though I use that in nearly all my scripts I completely forgot for about 20 min and went mad debugging the error.

Oh, and I have a piece of bad news — even though this script would be great to store as a Macro, as of today this can only be done in TE3. TE2 complains about compile errors while saving as macro — probably because of the extra curly bracket trick. In TE3 it took a while but it worked. I’m pretty sure that Daniel Otykier and his team will find a solution soon. Let him know you *need* this to work 😀

UPDATE (2022-10-28) The script (and any script with built in classes) can now be stored as macro in TE2 *and* without the curly bracket hack!

Anyway, here you can see the macro in action in TE3 and how it only creates the calculation item for relationships which are inactive.

So that’s about it!

If you want to play with it, here’s the script! It’s in TE2 format. (If you want to run in TE3, you’ll need to comment line 47 and 914 where the extra closing bracket and extra opening bracket are respectively.) Now it’s compatible with both TE2 and TE3!
And there’s probably not much point, but here’s the PBIX

More importantly if you only want the class you can take it from here  You only  need to copy from string utils to the 2nd curly bracked from the end. If you are working in TE2 remember the extra closing bracket before the beginning of the class and the extra open bracket at the very end.

 

Thank you so much for reading all the way until here!

Follow the conversation on Twitter and LinkedIn