Convert SQL to DAX

[Anonymous]

Will someone please convert this sql to dax for me?

case when nvl (STAFF_ABILITY_RESOLVE_ISSUES, 'N/A') = 'N/A' then null else to_number (STAFF_ABILITY_RESOLVE_ISSUES) end as atri,

I need to get the N/A values in the field to become nulls and at the same time convert the entire column to number format, specifically decimal if possible. I am in direct query mode in the query editor so it does not allow me to convert the column so I am hoping this statement will allow it!!

Thanks

[v-yulgu-msft]

Hi @Anonymous,

Please try this code.

=if  [STAFF_ABILITY_RESOLVE_ISSUES]= "N/A" then null else Decimal.From([STAFF_ABILITY_RESOLVE_ISSUES])

Alternatively, you can directly choose Decimal Number under the data type list. 

Best regards,

Yuliana Gu

[Phil_Seamark]

HI @Anonymous

Please try this

Column = 
    SWITCH(
        TRUE() ,
        ISNUMBER('Table1'[STAFF_ABILITY_RESOLVE_ISSUES]), FIXED('Table1'[STAFF_ABILITY_RESOLVE_ISSUES],2),
        BLANK()
        )

[Anonymous]

@Phil_Seamark Expression.Error: The name 'SWITCH' wasn't recognized.  Make sure it's spelled correctly.

[Phil_Seamark]

Hi there,

Are you using Power BI and are you doing this in DAX or in the Query Editor?

[Anonymous]

@Phil_Seamark Hey Phil! I am using query editor in power BI desktop. Custom Column

[parry2k]

all above solution were provided using DAX, if you are in query editor, on add customr column, you need to do following:

if  STAFF_ABILITY_RESOLVE_ISSUES = "N/A" then 
null
else STAFF_ABILITY_RESOLVE_ISSUES

[Anonymous]

@parry2k Hello, Can you help me with a forumal that will convert the entire column in to a decimal number format in the same statement? The problem is that even if I I substiture the N/A for null, I need the entire column to be number... the direct query mode doesn't allow me to just simply convert it to decimal format...

[Anonymous]

@parry2k 

I have tried using to_number and value but it didn't like those words:

if [AMENITIES] = "N/A" then null else to_number ([AMENITIES])

if [AMENITIES] = "N/A" then null else value ([AMENITIES])

Any ideas of a better word there that would be accepted?

Thank you in advance for your kind help!

[vega]

Does this work for you?

IF(
   STAFF_ABILITY_RESOLVE_ISSUES = "N/A", 
   BLANK(),
   VALUE(STAFF_ABILITY_RESOLVE_ISSUES)
)

[Anonymous]

@vega  Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly.

[Anonymous]

Expression.Error: The name 'IF' wasn't recognized.  Make sure it's spelled correctly.