IF with text and numbers

[aeterni]

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.

[Stachu]

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

[Stachu]

try this

Column =
IF (
    ISBLANK ( PowerBI[DueBy] ),
    BLANK (),
    IF (
        ISERROR ( VALUE ( PowerBI[SLA Alert] ) ),
        BLANK (),
        IF ( VALUE ( 'PowerBI'[SLA Alert] ) > 2, ">2", "<2" )
    )
)

[aeterni]

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.

[Stachu]

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?

[aeterni]

Hi,

below my regional settings on my PC.

Below my regional settings on PowerBI

My PowerBI version is 2.71.5523.941 64-bit (July 2019)

Below a screenshot of the error

Indeed my "Decimal Separator" and "List separator" is the same ","

Would you suggest to change one of the two...and if yes which symbol should I use?

Thanks

[Stachu · Accepted solution]

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

[aeterni]

Hi,

• I changed my local settings --> '.' as decimal separator
• I rebuilt my DB...otherwise I still had the "," as decimal separator

It works now.

Thank you.