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

[dsabsi]

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

[v-ljerr-msft]

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. 

Table1

column1	column2
a	1
b	2
c	3

Expect Result: Total of 'column2': 6

Regards

[dsabsi · Accepted solution]

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

[MFelix]

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

[dsabsi]

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

[Dog]

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")

[MFelix]

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