question

BabuKumar-3948 avatar image
0 Votes"
BabuKumar-3948 asked Konstantin-2382 commented

Azure Analysis Service: Error using Calculation Groups

Hi,

I was successfully using Calculation Groups before. Now suddenly I get the following error whenever I try to use the Calculation Group. Nothing changed except of my upgrade for VS 2019 from 16.5.5 to 16.6.0 and Analysis Service extension from 2.9.9 to 2.9.10:

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

The DAX query works if I don't include the Calculation Group. The same query with Calculation Group works fine in a instance deployed to the Server before my VS upgrade.

I even did downgrade for the extension still not able to resolve.

Thanks.

azure-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BabuKumar-3948 ,

Thanks for reaching out. I have noticed that you have filed a SR for this issue and a support engineering is looking to into it. Please do consider to update this thread once the issue is resolved.




Thanks!

0 Votes 0 ·
KranthiPakala-MSFT avatar image
1 Vote"
KranthiPakala-MSFT answered

Hi @BabuKumar-3948, All,

From the support ticket analysis, it is observed that the issue is with latest version of VS, which automatically changed the column order of calculation group. Hence the problem with model. Calculation Group requires following column order in the tabular model. At this time still we have been waiting for the update from the developer team to confirm if its Visual Studio Bug and if yes, possible ETA for permanent fix. Will keep this thread posted as soon as we have further update from internal teams.

       "columns": [
         {
           "name": "Time Calculation",
           "dataType": "string",
           "sourceColumn": "Name",
           "sortByColumn": "Ordinal"
         },
         {
           "name": "Ordinal",
           "dataType": "int64",
           "isHidden": true,
           "sourceColumn": "Ordinal"
         }
       ],

Until then the workaround to fix/overcome this issue is by deleting calculation groups using below tmsl scripts and then recreating calculation groups using sub-scripts with fixed order:

 {
   "delete": {
     "object": {
       "database": "testDB1",
       "table": "testTable1"
     }
   }
 }
    
 {
   "delete": {
     "object": {
       "database": "testDB2",
       "table": "testTable2"
     }
   }
 }


Hope this info helps.




Thank you

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HelgeSchroederPrivat-2560 avatar image
2 Votes"
HelgeSchroederPrivat-2560 answered

In my experience this can happen when you choose a name for the column containing the calculation items, which is alphabetically after "Ordinal".
Then it gets deployed in the wrong order, and you get the comparison-error (because it is trying to use the wrong column as sorting/ordinality.

I got this error today, deploying from Tabular Editor 2.14.2.

The solution was to delete the calculation group, deploy, undo deletion, choose column name which is alphabetically before "Ordinal", deploy again.

Hope this helps someone :)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Harvey-6837 avatar image
0 Votes"
Harvey-6837 answered

Thank You. Unbelievable. Spent over a day trying every other possible tweak until I found this. That would never have occurred to me.

I would say its not a bug in VS or TabEd. It should not matter what order the columns are stored in XML/JSON, AnalysisServices should be accessing Ordinal by name.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JrgJenny-3942 avatar image
1 Vote"
JrgJenny-3942 answered Konstantin-2382 commented

I had this issue today. A workaround is renaming the "Ordinal" column to "zzOrdinal".

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much for this workaround. Helped me a lot today!

0 Votes 0 ·