Forum Discussion

jtgriffin's avatar
jtgriffin
Frequent Visitor
6 years ago
Solved

Else If Formula Breaks One Dataset

Hello, 

 

I have a custom column that I want to add to my data that contains an else if formula. I have used this formula in the past in Power BI with no problems, however it will only return "ERROR" in the cell now. 

 

The Formula: = if [#"Ceiling Value ($K)/Contract Value"]>49999 and [#"Ceiling Value ($K)/Contract Value"]<99999 then "50-100M" else if [#"Ceiling Value ($K)/Contract Value"]<50000 then "Less than 50M" else if [#"Ceiling Value ($K)/Contract Value"]>100000 then "Greater than 100M" else "N/A"

 

Essentially I want it to read the values in "Ceiling Value($K)/Contract Value" and write into my new column what statement they fit into (50-100M, Greater than 100M, etc." But currently it will only return "Error." It has no syntax errors so I'm confused. Even checked the formula from another dashboard I have and its working fine. 

 

Any idea how to fix this? 

 

Thanks, 

JT

Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    jtgriffin 

     

    Looks like M query. What was the error you got?

     

    Click on the cell which say error to see it.

     

    If this helps, mark it as a solution

    Kudos are nice too

5 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    jtgriffin 

     

    Looks like M query. What was the error you got?

     

    Click on the cell which say error to see it.

     

    If this helps, mark it as a solution

    Kudos are nice too

    • jtgriffin's avatar
      jtgriffin
      Frequent Visitor
      6 years ago

      The error I get is as follows:

       

      Expression.Error: We cannot apply operator < to types Number and Text.
      Details:
      Operator=<
      Left=50000
      Right=484

       

      The 484 is the number in column "Ceiling Value ($K)/Contract Value". So it seems to be looking in the right spot. Any ideas why it wont apply the < operator? 

       

      This is very strange. 

       

      Thanks, 

      JT

      • jtgriffin's avatar
        jtgriffin
        Frequent Visitor
        6 years ago

        Im new to PBI, but I think this was the issue. 

         

        I had the add column query placed after I told it to transform the data to decimal form for the ceiling value. Once I moved the data change type above the add custom column everything seemed to work. 

         

        Did think order mattered but apparently it does in that section. 

         

        Thanks for all the help!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    This M query should work

    =if [#"Ceiling Value ($K)/Contract Value"]<50000 then "Less than 50M" else if [#"Ceiling Value ($K)/Contract Value"]>100000 then "Greater than 100M" else "50-100M"

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi jtgriffin ,

     

    Sorry for that we cannot reproduce this error on my side, Could you please try to verify the Name and Data Type of Column Ceiling Value($K)/Contract Value?

     

    if [#"Ceiling Value($K)/Contract Value"]>49999 and [#"Ceiling Value($K)/Contract Value"]<99999 then "50-100M" else if [#"Ceiling Value($K)/Contract Value"]<50000 then "Less than 50M" else if [#"Ceiling Value($K)/Contract Value"]>100000 then "Greater than 100M" else "N/A"

     


    Best regards,