Tabular Model – Help – SortByColumn property set to an invalid column ID

Problem statement

Since a couple of weeks, we have been redesigning our SSAS Tabular Model. We are keeping the model as user-friendly as possible and we try to minimize overhead for our end-users to a bare minimum.

During this process, we were configuring certain properties for every column in our model like Display Folder, Hidden, Summarize By, Display Ordinal, Available in MDX, Translations, Shown in Perspective…

After making these changes, we pushed our changes into Azure DevOps and our deployment pipeline started to deploy the changes to the requested environment.

While the deployment process was executing, it stopped and failed promptly. We ran into an issue: “SortByColumn property set to an invalid column ID”

Solution

Because of this error, we started the investigation. We opened our model in Tabular Editor and went to the column that was causing trouble during deployment.

In the screenshot below, I have highlighted the changes we made.

To know the property that is causing this issue, we used elimination to identify the issue. Since the Hidden property is just hiding a certain object for end users, this could be taken from the list of potential causes straight away.

Following that same thought, we could eliminate Object Level Security, and Show in Perspective as well.

Since the “Summarize By”-property only affects the default applied function during analysis, we couldn’t believe this was causing the issue either.

Leaving the “Available in MDX”-property as the only potential explanation for this issue. To test, we re-enabled the “Available in MDX”-property, committed our change, and let the deployment pipeline try to deploy the change again.

After a few moments, we were able to see that our deployment went successfully. So why did this property cause us this issue?

In our redesigned model we are using the FiscalMonthNr Column to sort our Fiscal Month column properly. As you can see in the screenshot below.

If you want to sort on a particular column, you should leave the “Available in MDX”-property enabled.

Why do I want to disable the “Available in MDX”-property?

Behind the scenes, this option is used to determine if an Attribute Hierarchy can be created on this column. This has an immediate impact on your Tabular Model size and processing time.

To minimize the processing time and size of my model, I try to disable this for as many columns as possible.

Mostly for columns that are used to build measures. Since attribute hierarchies based on these columns just don’t make sense. But I’m disabling this for the Primary/Foreign Key columns in my fact and dimension tables as well since these are not really useful for my end-users anyway.