cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CPNLddl Frequent Visitor
Frequent Visitor

Measures and numbers can't be used in IF formula anymore it seems

Hi, 

 

I really hope you can help me. I've written some calculated columns with IF statements. These all worked, but now that I want to adjust my formula, the old one doesn't even work anymore.

This happens in IF statements where the logical test is to define values. 

 

I tried creating a new IF formula to see what goes wrong, and the following happens;

I can't use numbers in the logical test part anymore. I've checked that the column that I'm referring to is a number, but when I write

 

 

=IF([Number of received emails]>10,">10","Other")

 

[number of received emails] is a measure

 

Power BI doesn't recognize this as a logical test anymore, I can only use text values in the logical test. This wasn't the case before, does anyone know why this happens?

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
CPNLddl Frequent Visitor
Frequent Visitor

Re: Measures and numbers can't be used in IF formula anymore it seems

Hi, Thanks!

I will use this formula, that looks good Smiley Happy

I found out what the problem was: my language settings weren't English, which caused Power BI to not recognize the formula. Really appreciate the fast replies, super nice, thank you again!

4 REPLIES 4

Re: Measures and numbers can't be used in IF formula anymore it seems

@CPNLddl

 

I was not able to replicate the problem you are running into but let me know if I missed something.

 

Tables of data that has a count of emails.

logic_emails.JPG

 

A measure to sum the # of emails

# Emails = SUM(Emails[emails])

The data table is joined to my users table and I was able to apply the logic in the Check column.

logic_users.JPG

 

This is what youare trying to do, yes? 

CPNLddl Frequent Visitor
Frequent Visitor

Re: Measures and numbers can't be used in IF formula anymore it seems

Yes, exactly!

And in your example it works, but in my version it doesn't..

 

I've also opened a new Power BI document, and loaded data with a column 'employees national', this field type is whole number. And then also the IF statement doesn't recognize the logical test.

 

 

The IF statement only works when I put text in the logical test while I need to use functions for numbers. 

Here's the whole formula:

Range received formula doesn't work.PNG

 

And here's an example of what happens when I try to use the IF statement with numbers/values:

 

emp nat.pngEmployees national.pngin the description the 'ResultIfTrue should now be bold, but it's not. If I replace >100 for ="100" the formula is recognized, but that's not correct, nor what I need.

 

can you help? Smiley Happy

 

Re: Measures and numbers can't be used in IF formula anymore it seems

You need a space between your check and the && so instead of

if([Sum Received Organixation])>0&&

if([Sum Received Organixation])>0 &&

 

You can also invert your formula to make it easier, start from the top end and work down, that way you don't need to use &&

 

Range Received = 

IF ([Sum Received Organisation] > 300 , "f. +300",

IF ([Sum Received Organisation] > 200 , "e. 201-300",

IF ([Sum Received Organisation] > 100 , "d. 101-200",

IF ([Sum Received Organisation] > 50 , "c. 51-100",

IF ([Sum Received Organisation] > 10 , "b 11-50",

IF ([Sum Received Organisation] > 0 , "a 1-10",

IF ([Sum Received Organisation] = 0 , "0" , "Error" )))))))

Highlighted
CPNLddl Frequent Visitor
Frequent Visitor

Re: Measures and numbers can't be used in IF formula anymore it seems

Hi, Thanks!

I will use this formula, that looks good Smiley Happy

I found out what the problem was: my language settings weren't English, which caused Power BI to not recognize the formula. Really appreciate the fast replies, super nice, thank you again!