Forum Discussion
IF with text and numbers
Hello,
I have a two columns table:
- Column A: it has either blank cells or date and time
- Column B: it has either numbers or text
I'd like to create a third column:
- if column A is BLANK --> BLANK
- if column B is TEXT --> BLANK
- if column B is greater than 2 --> >2
- else --> <2
I wrote this formula, but I get the following error message: Cannot convert value 'SLA Breached' of type Text to type Number.
SLA >3 = if(ISBLANK(PowerBI[DueBy]),BLANK(),IF(VALUE(PowerBI[SLA Alert])<2,,1,,0))
I also tried with this formula, but I get the following error message: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.
SLA >3 = if(ISBLANK(PowerBI[DueBy]),BLANK(),if(IFERROR(PowerBI[SLA Alert],BLANK()),BLANK(),IF(PowerBI[SLA Alert]<2,,1,,0)))
Both error messages are clear, but I can't find a solution.
Thanks for your help.
Regards.
OK, so first let's talk about the syntax first
What works for you is this:
IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2,, 1,, 1 )
the thing is '2,' is atually the same as '2.' for me, or how '2,0' would be for you
IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2,, 1,, 1 )
the red commas are a decimal separator, the blue ones are list separator. That's why this should work as well:
IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2,0, 1,0, 1 )
As you use modified English settings, I'd suggest using the default option for decimal - the dot '.' and comma as list separator
now the logical problem with this IF (I will use '.' as decimal separator to make it more clear)
IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2., 1., 1 )
it returns '1.' for TRUE and '1' for FALSE. Which is always 1 with whatever is the formatting in your column. You need to change one of the '1' to something that you want to see, like I posted earlier:
Test = IF ( ISBLANK ( PowerBI[DueBy] ), BLANK (), IF ( ISERROR ( VALUE ( PowerBI[SLA Alert] ) ), BLANK (), IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2 , 2 , 1 ) ) )'2' for TRUE and '1' for FALSE
7 Replies
try this
Column = IF ( ISBLANK ( PowerBI[DueBy] ), BLANK (), IF ( ISERROR ( VALUE ( PowerBI[SLA Alert] ) ), BLANK (), IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2, ">2", "<2" ) ) )Hi,
thank you for your feedback.
I changed slightly your formula because there were a couple of syntax error...at least my PowerBi app was throwing syntax errors:
- ">2" and "<2" --> syntax error - replaced with "1" and "2" for test purpose
- two commas are necessay in the last IF statement
Test = IF ( ISBLANK ( PowerBI[DueBy] ), BLANK (), IF ( ISERROR ( VALUE ( PowerBI[SLA Alert] ) ), BLANK (), IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2,, 1,, 1 ) ) )In general the formula works because it filters out the text (I get indeed blank cells when the formula hits a text cell), however the last IF statement doesn't work because I get only "1", but I have plenty of values less than 2.
Regards.
interesting, what are your regional settings? specifically list separator, decimal separator and text identifier
the syntax I posted is correct, it was even formatted using https://www.daxformatter.com/
it correctly returns "<2" and ">2" respectively
your syntax is not working for me though - it passes to many arguments for IF (which is expected with 4 commas)
DAX formatter returns error as well with your syntax
what's your PowerBI version? can you post a screenshot of the syntax error that's return when you try the code I posted?
hmm, I guess if both your decimal sign and list separator are set to comma your syntax would be correct. and if that's your setting then anything can be the text identifier, so no surprise the " give error
if that's the case (although I'm not sure OS would allow such risky setup) this syntax should work
Test = IF ( ISBLANK ( PowerBI[DueBy] ), BLANK (), IF ( ISERROR ( VALUE ( PowerBI[SLA Alert] ) ), BLANK (), IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2 , 2 , 1 ) ) )it returns 2 for >2 and 1 for <=2
