Forum Discussion
IF with text and numbers
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
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?
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
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
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.