Inumber not working in calculated column.

[adilk]

Hi,

I have a particular column [Search Impr. share] that has three possible values:

<10%

--

x%

If the column has x%, I want to be able to find out the eligible impressions. This would be created by dividing [actual impressions] column by [Search impr. share.]. I would create a calcuated field for this once I know which rows to use.

I tried creating a calculated column using ISNumber but I get all results as "No"

Eligible Impession = if(isnumber('Weekly Search'[Search Impr. share]),"Yes","No")

If I try to extract the first character of the [Search Impr. share] and multiply it by 1 (to convert it to a number), PBI doesn't accept it as the column can have both string and numeric values.

can anyone help with this, please.

Thanks.

[v-huizhn-msft]

Hi @adilk,

In your resource table, if the x% follow certain roles in the [Search Impr. share] field? If it does, you can split the column using Split function in Edit Query, then you can use the sun-column to discriminate.

For example I have the following sample data.

result = IF(Table8[Test.2]="X%","Yes","No")

 
Click the table->Edit Query->Select Split columns->By Number Column of Characters , you will get the following window, you can set your roles according to your requriement.



Click close&aplly, you will get the new table, please create calculated column using the formula, and you will get the expected result.

result = IF(Table8[Test.2]="X%","Yes","No")

If this is not what you want, please post the sample data for further analysis.

Best Regards,
Angelia

[adilk]

Hi @v-huizhn-msft

Thanks for replying here. By X%, I meant that that the value could be a % value.

So, the three types of possible values in this column are:

--, <10% or 5%,10% (any actual % number).

How would the solution change based on this input?

-adil

[v-huizhn-msft]

Hi @adilk,

You can use the following formula. Please add mutiple If.. Else condition, until all Table[column] values are included.

result = IF(Table[column]<x%,"Yes",IF(Table[column]<Y%,"Yes","No"))

 Best Regards,
Angelia