Searching column for values < 2000

[Gustav_Juel_Hal]

Hi,

I am having trouble creating a calculated column, where I search another column for values < 2000.

IF < 2000, then it shall return "Home" and IF > 2000, then "Out".

I have tried something like this

     _Out_Home =
           IF(Value('DM_Employee'[PersiumOrgID]) < 2000, "Home", "Out")

The column consists of non unique IDs in text format, which is why I use the Value function.

When i do this it says: " Cannot convert value '' of type Text to type Number. "

I have also tried this:

_Out_Home =    

CALCULATE('DM DimEmployee',

      FILTER('DM DimEmployee', 'DM DimEmployee'[PersiumOrgID] < 2000)

This gives me the fault message: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

The data is confidential, but the column looks something like this:

PersiumOrgID

1345

1345

1345

1423

1464

1476

1488

1488

1488

1502

2012

2045

2045

2045

2062

2078

Hope you can help, thanks 🙂

[v-luwang-msft · Accepted solution]

Hi @Gustav_Juel_Hal ,

I test the data your provided ,run successful(see the below).

Cannot convert value '' of type Text to type Number: It might be because one of the rows contain blank value or non numberic value. In query editor, try filtering each column for a non numeric value.  

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value：refer :

https://www.sqlgene.com/2019/04/30/dax-error-the-expression-refers-to-multiple-columns-multiple-colu... 

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@Gustav_Juel_Hal , In case you want to create a new column

_Out_Home =
IF('DM_Employee'[PersiumOrgID] < 2000, "Home", "Out")