Searching column for values

Question

Hi,&nbsp;I am having trouble creating a calculated column, where I search another column for values &lt; 2000.&nbsp;IF &lt; 2000, then it shall return "Home" and IF &gt; 2000, then "Out".&nbsp;I have tried something like this&nbsp; &nbsp; &nbsp;_Out_Home =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF(Value('DM_Employee'[PersiumOrgID]) &lt; 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: "&nbsp;Cannot convert value '' of type Text to type Number. "&nbsp;I have also tried this:_Out_Home =&nbsp; &nbsp;&nbsp;CALCULATE('DM DimEmployee',&nbsp; &nbsp; &nbsp; FILTER('DM DimEmployee', 'DM DimEmployee'[PersiumOrgID] &lt; 2000)This gives me the fault message: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."&nbsp;The data is confidential, but the column looks something like this:&nbsp;PersiumOrgID1345134513451423146414761488148814881502201220452045204520622078&nbsp;Hope you can help, thanks 🙂&nbsp;

v-luwang-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
I test the data your provided ,run successful(see the below).

Cannot convert value '' of type Text to type Number:&nbsp;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. &nbsp;
&nbsp;
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-columns-cannot-be-converted-to-a-scalar-value/&nbsp;
&nbsp;
Wish it is helpful for you!
&nbsp;
Best Regards
Lucien

Forum Discussion

Searching column for values < 2000

2 Replies

Related content

Searching the value and updating it in new columns.

Splitting Column values

search multiple values (google-like search field)

Matching Column Values

Slicer Search Bar