SSAS Tabular: “SortByColumn property set to invalid column ID”

Recently I was doing a cleanup of one model, and I used Best Practice Analyzer (BPA) in Tabular Editor as a helper. Later, I realized I forgot to set up sort order by other column for some of the columns in the model. However, when trying to save model with new sort order property I got following error:

The message: “The column X in table Y has the SortByColumn property set to invalid column ID”

It took me some time to discover the source of the issue for this confusing message. During the cleanup of the solution, BPA proposed to disable MDX hierarchy for some of the columns and I accepted this optimization.

However, you cannot use column which has “Available in MDX” attribute set to false for sorting and that was the source of the issue. Of course, this is not mistake of BPA but mine. If you have column used for sorting BPA never propose you disable this property.

So be careful in setting of “Available In MDX” property as it might have weird consequences. Please check following interesting comment on this.

I have around 20 years of experience with MS data platform. In last couple years I’m passionate mainly about PB/SSAS modeling and DAX. Proud father of three boys who teach me to live 🙂

ROMAN LÁNSKÝ
BI Architect
LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed