Else If Formula Breaks One Dataset

[jtgriffin]

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

[v-lid-msft]

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,

[Ashish_Mathur]

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"

[VasTg · Accepted solution]

@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]

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]

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!