Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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
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,
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"
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
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
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!