Forum Discussion
operand error for sum with directquery
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
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
4 Replies
- amitchandak
Super User
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
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.
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
Thanks for the solution. It work well
