cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Error: The function SUM can not work with values of type String

Hi all, 

 

I'm new to Power BI and I'm trying to visualize my IF-formula but I'm getting this error: 

The function SUM cannot work with values of type String. 

I tried to remove the sum from my formula but then it won't get the correct tab. 

I tried to add the relationship in the tab' Manage relationships'  but that doesn't work because it then states the following error:

 

You can't create a direct active relationship between xxx and xxx because that would introduce ambiguity between tables xxx and xx. To make this relationship active, deactivate or delete one of these relationship between xx and xxx first.  

 

My formula is (i've put the tables as xxx since it's confidential information):  

If(sum( xxx)="";"Not an option"; if(sum(xxx)="";"Plausible option 2";"No way""))

 

I hope you guys can help me out, I'm sorry that I can't give you the exact table names. 

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

Re: Error: The function SUM can not work with values of type String

Hi guys, 

 

I found the solution!

I created a new measure, while it should be new column with the same If-formula and now it works =D I'm really happy!!

I do want to emphasize my gratitude for your swift replies, I'm really blown away how many people are willing to help :). 

 

Many thanks and have a great day!

 

Cheers

View solution in original post

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Error: The function SUM can not work with values of type String

Hi @dsabsi,

 

I believe that the question is regarding your column type of data, is your column a number, do you have the E icon on the column?

 

The expression that you created is ok but if the field is not of the type number it cannot calculate, personnaly I would substitute this by a SWITCH formula that allows a better visualization of you calculations:

 

SWITCH ( TRUE(),
SUM(Table[Colum] = Result1; "Not an Option";
SUM(Table[Colum] = Result2; "Plausible option 2";
"No Way")

 

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Advocate I
Advocate I

Re: Error: The function SUM can not work with values of type String

Hello MFelix, 

 

I really appreciate your swift reply!

I tried to use your formula as measure but it gives me the following error: Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of these values.

 

Both columns to which I'm referring are Text (data type and format), so there is no E icon before my columns. 

Moreover, I can't change the data type of my measure, do you have any idea how I can show my if-formula in another way or perhaps I'm doing something wrong with your proposed solution :).


Any help is really appreciated! 

 

Cheers

 

Highlighted
Super User III
Super User III

Re: Error: The function SUM can not work with values of type String

HI @dsabsi,

 

If you are using Text fields you cannot use the Sum function, why are you comparing text strings with sum? Can you proveid a little bi more detail on the values / data you have in your columns?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Responsive Resident
Responsive Resident

Re: Error: The function SUM can not work with values of type String

Hi @dsabsi

 

agree with Felix, it's quite hard to give the correct solution without knowing some detail about the data types etc... at this stage I'm not sure if you require SUM, if you don't and you are looking at text comparisons then you could try the following. (I'm assuming that you have some form of row level filtering in the visual. (eg. a table which loads the 'TableName'[ColumnName] data)

 

MyMeasure:=

var Res = IF(ISFILTERED('TableName'[ColumnName]), VALUES('TableName'[ColumnName]))

 

Return

SWITCH(Res,

     "result1", "Not an option",

     "result2", "Plausible option 2",

     "No Way")

 

 

Highlighted
Microsoft
Microsoft

Re: Error: The function SUM can not work with values of type String

Hi @dsabsi,

 

Could you post some sample data(no need to post the real data, just some letters or random numbers like below) and your expected result? So that we can better assist on this issue. Smiley Happy

 

Table1

column1 column2
a 1
b 2
c 3

 

Expect Result: Total of 'column2': 6

 

Regards

Highlighted
Advocate I
Advocate I

Re: Error: The function SUM can not work with values of type String

Hi guys, 

 

I found the solution!

I created a new measure, while it should be new column with the same If-formula and now it works =D I'm really happy!!

I do want to emphasize my gratitude for your swift replies, I'm really blown away how many people are willing to help :). 

 

Many thanks and have a great day!

 

Cheers

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors