Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a two columns table:
I'd like to create a third column:
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.
Solved! Go to 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
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
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:
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,
It works now.
Thank you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.