operand error for sum with directquery

[Malo]

Dear all,
I've an issue with the last version of Power BI (2.84.981.0 64-bit (august 2020)). I've opened an old report, last modified on 08th June 2020 (it work well with no problem), and I've added a new table from my database (the same of the report) using SQL server and directquery, no problems in this operation. I've converted the data type of a column from nvarchar to integer in the main page and then I've created a simple measure (SUM (table_name [column_name]), but when I've tried to create a visual object is occurred this error "OPERAND DATA TYPE NVARCHAR IS INVALID FOR SUM OPERATOR..". How I can solve the problem? I know that with directquery I can not change data type in power query mode, but I don't want to shape data before import. 

Thanks for your help

[v-jayw-msft · Accepted solution]

Hi @Malo ,

You could create a measure using VALUE() function to convert text to number.

Then use SUMX() function to calculate the sum of measure.

Measure = VALUE(SELECTEDVALUE('Table'[Column1]))

Measure 2 = SUMX('Table',[Measure])

 
Best Regards,

Jay

[Malo]

Thanks for the solution. It work well

[amitchandak]

@Malo , please check the data type of the column at the source. It showing it as nvarchar . So you can only have Count /min/max .

Try sum on some numeric column it should work

[Malo]

Thanks for your suggestion

If I try to repeat the same operation on a table already imported, I've no problem. For this reason I've tried to re import like a new table, one I've already imported and I've repeated the same operations, but this time nothing work as in the past.