Forum Discussion

dsabsi's avatar
dsabsi
Advocate I
9 years ago
Solved

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

Need Help
  • dsabsi's avatar
    dsabsi
    9 years ago

    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

6 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

     

    Table1

    column1 column2
    a 1
    b 2
    c 3

     

    Expect Result: Total of 'column2': 6

     

    Regards

    • dsabsi's avatar
      dsabsi
      Advocate I
      9 years ago

      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's avatar
    MFelix
    Super User
    9 years ago

    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's avatar
      dsabsi
      Advocate I
      9 years ago

      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's avatar
        Dog
        Responsive Resident
        9 years ago

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